﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Framework_Tool
{
    class Database
    {
        public static int userX;
        private static MySqlConnection con = new MySqlConnection("server= 127.0.0.1; UId=root; database=Framework_Tool; Password=;");

        private static void connection()
        {
            con.Open();
        }

        public static String Login(string userIn, string passwordIn, MainWindow main)
        {
            String name = null;
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                // string enc = Encryption.StaffPwd(passwordIn);

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("Select staff.staffID, person.password, person.firstName, " +
                        "person.surname FROM staff INNER JOIN person ON staff.userID = person.userID " +
                    "WHERE staff.staffID='" + userIn + "' AND person.password ='" + passwordIn + "';");

                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            main.stfLogged_in();
                            StaffLoggedIn stfLoggedInWindow = new StaffLoggedIn();
                            stfLoggedInWindow.Content = null;
                            main.mainContent.Children.Add(stfLoggedInWindow.mainContent);
                            name = (dr.GetString(2) + " " + dr.GetString(3));
                        }

                        if (dr.HasRows == false)
                        {
                            MessageBox.Show("Access Denied", "Login Information");
                        }
                    }
                }
                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            return name;
        }

        // *************************************************** STAFF ***************************************************

        // *-*-*-* Staff View Methods *-*-*-*

        public static void populateStaffListview(StaffView StaffViewIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT p.userID, p.firstName, p.surname, s.jobTitle, p.email, "
                        + "p.telNo FROM person p JOIN staff s ON p.userid = s.userID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "staffPerson");
                    
                    foreach (DataRow myRow in ds.Tables["staffPerson"].Rows)
                    {
                        StaffViewIn.displayStaffDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Staff Add Methods *-*-*-*

        public static void CreateStaff(StaffAdd StaffAddIn)
        {
            try
            {
                string nextStfID = NextStaffID();

                StaffAdd stfA = StaffAddIn;

                bool validEmail = IsValidStfEmail_StaffAdd(StaffAddIn);
                if (!validEmail)
                {
                    return;
                }

                bool validPassword = ValidatePassword_StaffAdd(StaffAddIn); // checks that password is validated
                if (!validPassword)
                {
                    return;
                }

                bool matchingPasswords_stfA = ComparePasswords_staffAdd(StaffAddIn);
                if (!matchingPasswords_stfA)
                {
                    return;
                }

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT person (userID, firstName, surname, aLine1, aLine2, aCity," +
                        "aCounty, aPostcode, telNo, email, password) " +
                        "VALUES ('" + nextStfID + "', '" + stfA.stfFNameTxt.Text + "', '" + stfA.stfSurnameTxt.Text + "', '" +
                        stfA.stfALine1Txt.Text + "', '" + stfA.stfALine2Txt.Text + "', '" + stfA.stfCityTxt.Text + "', '" +
                        stfA.stfCountyTxt.Text + "', '" + stfA.stfPostcodeTxt.Text + "', '" + stfA.stfTelNoTxt.Text + "', '" +
                        stfA.stfEmailTxt.Text + "', '" + stfA.stfPasswordAddTxt.Password + "')");
                    int a = cmd.ExecuteNonQuery();

                    cmd.CommandText = string.Format("INSERT staff (jobTitle, userID) " +
                                "VALUES ('" + stfA.stfJobTitleTxt.Text + "', '" + nextStfID + "');");
                    int b = cmd.ExecuteNonQuery();

                    if (a != 0 && b != 0)
                    {
                        con.Close();
                        stfA.stfAddedLblGrid.Visibility = Visibility.Visible;
                        stfA.stfMemberAddedLbl.Content = ("Staff Member: " + StaffAddIn.stfIDAddTxt.Text + " added");
                        stfA.stfFNameTxt.Text = "";
                        stfA.stfSurnameTxt.Text = "";
                        stfA.stfALine1Txt.Text = "";
                        stfA.stfALine2Txt.Text = "";
                        stfA.stfCityTxt.Text = "";
                        stfA.stfCountyTxt.Text = "";
                        stfA.stfPostcodeTxt.Text = "";
                        stfA.stfTelNoTxt.Text = "";
                        stfA.stfEmailTxt.Text = "";
                        stfA.stfJobTitleTxt.Text = "";
                        stfA.stfIDAddTxt.Text = NextStaffID();
                        stfA.stfPasswordAddTxt.Password = "";
                        stfA.stfPassword1AddTxt.Password = "";
                        stfA.matchStfPasswordLbl.Visibility = Visibility.Hidden;
                        stfA.validStfPasswordLbl.Visibility = Visibility.Hidden;
                        stfA.stfEmailValidationLbl.Visibility = Visibility.Hidden;
                    }

                    else
                    {
                        con.Close();
                        stfA.stfMemberAddedLbl.Content = ("Staff member: " + StaffAddIn.stfIDAddTxt.Text + " not added");
                    }  
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static string NextStaffID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }

            string nextStaffID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'staff'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextStaffID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
                con.Close();
                return nextStaffID;
        }

        public static bool IsValidStfEmail_StaffAdd(StaffAdd StaffAddIn)
        {
            string email = StaffAddIn.stfEmailTxt.Text;
            Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
            Match match = regex.Match(email);
            
            if (match.Success)
            {
                StaffAddIn.stfEmailValidationLbl.Visibility = Visibility.Visible;
                StaffAddIn.stfEmailValidationLbl.Content = "Valid email";
                StaffAddIn.stfEmailValidationLbl.Foreground = Brushes.Green;
                return true;
            }
            else
            {
                StaffAddIn.stfEmailValidationLbl.Visibility = Visibility.Visible;
                StaffAddIn.stfEmailValidationLbl.Content = "Not a valid email";
                StaffAddIn.stfEmailValidationLbl.Foreground = Brushes.Red;
                return false;
            }
        }

        public static bool ValidatePassword_StaffAdd(StaffAdd StaffAddIn)
        {
            const int MIN_LENGTH = 8;
            const int MAX_LENGTH = 15;

            if (StaffAddIn.stfPasswordAddTxt.Password == "") throw new ArgumentNullException();

            bool meetsLengthRequirements = StaffAddIn.stfPasswordAddTxt.Password.Length >= MIN_LENGTH && StaffAddIn.stfPasswordAddTxt.Password.Length <= MAX_LENGTH;
            bool hasUpperCaseLetter = false;
            bool hasLowerCaseLetter = false;
            bool hasDecimalDigit = false;

            if (meetsLengthRequirements)
            {
                foreach (char c in StaffAddIn.stfPasswordAddTxt.Password)
                {
                    if (char.IsUpper(c)) hasUpperCaseLetter = true;
                    else if (char.IsLower(c)) hasLowerCaseLetter = true;
                    else if (char.IsDigit(c)) hasDecimalDigit = true;

                }
            }

            bool isValid = meetsLengthRequirements
                            && hasUpperCaseLetter
                            && hasLowerCaseLetter
                            && hasDecimalDigit;

            if (isValid == true)
            {
                StaffAddIn.validStfPasswordLbl.Visibility = Visibility.Visible;
                StaffAddIn.validStfPasswordLbl.Content = ("Valid password");
                StaffAddIn.validStfPasswordLbl.Foreground = Brushes.Green;
            }
            else
            {
                StaffAddIn.validStfPasswordLbl.Visibility = Visibility.Visible;
                StaffAddIn.validStfPasswordLbl.Content = ("Not a valid password");
                StaffAddIn.validStfPasswordLbl.Foreground = Brushes.Red;
            }

            return isValid;
        }

        public static bool ComparePasswords_staffAdd(StaffAdd StaffAddIn)
        {
            if (StaffAddIn.stfPasswordAddTxt.Password == StaffAddIn.stfPassword1AddTxt.Password)
            {
                StaffAddIn.matchStfPasswordLbl.Visibility = Visibility.Visible;
                StaffAddIn.matchStfPasswordLbl.Content = ("Passwords match");
                StaffAddIn.matchStfPasswordLbl.Foreground = Brushes.Green;
            }

            if (StaffAddIn.stfPasswordAddTxt.Password != StaffAddIn.stfPassword1AddTxt.Password)
            {
                StaffAddIn.matchStfPasswordLbl.Visibility = Visibility.Visible;
                StaffAddIn.matchStfPasswordLbl.Content = ("Passwords do not match");
                StaffAddIn.matchStfPasswordLbl.Foreground = Brushes.Red;
            }

            return String.Compare(StaffAddIn.stfPasswordAddTxt.Password, StaffAddIn.stfPassword1AddTxt.Password, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
        }
        
        // *-*-*-* Staff Update Methods *-*-*-*

        public static void FindStaffID_Update(StaffUpdate StaffUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    StaffUpdateIn.stfIDUpdateCmbx.Items.Add(dt.Rows[i]["staffID"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_StfUpdate(StaffUpdate StaffUpdateIn)
        {
            try
            {
                StaffUpdate stfU = StaffUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person JOIN staff ON person.userID = staff.userID " +
                "WHERE person.userID = '" + stfU.stfIDUpdateCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    stfU.stfFNameTxt.Text = dt.Rows[0]["firstName"].ToString();
                    stfU.stfSurnameTxt.Text = dt.Rows[0]["surname"].ToString();
                    stfU.stfALine1Txt.Text = dt.Rows[0]["aLine1"].ToString();
                    stfU.stfALine2Txt.Text = dt.Rows[0]["aLine2"].ToString();
                    stfU.stfCityTxt.Text = dt.Rows[0]["aCity"].ToString();
                    stfU.stfCountyTxt.Text = dt.Rows[0]["aCounty"].ToString();
                    stfU.stfPostcodeTxt.Text = dt.Rows[0]["aPostcode"].ToString();
                    stfU.stfTelNoTxt.Text = dt.Rows[0]["telNo"].ToString();
                    stfU.stfEmailTxt.Text = dt.Rows[0]["email"].ToString();
                    stfU.stfJobTitleTxt.Text = dt.Rows[0]["jobTitle"].ToString();
                    stfU.stfPasswordUpdateTxt.Password = dt.Rows[0]["password"].ToString();
                    stfU.stfPassword1UpdateTxt.Password = dt.Rows[0]["password"].ToString();

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void UpdateStaff(StaffUpdate StaffUpdateIn)
        {
            try
            {
                string nextUID = NextStaffID();

                StaffUpdate s = StaffUpdateIn;
                
                bool validEmail = IsValidStfEmail_Update(StaffUpdateIn);
                if (!validEmail)
                {
                    return;
                }
                
                bool validPassword = ValidatePassword_StfUpdate(StaffUpdateIn); // checks that password is validated
                if (!validPassword)
                {
                    return;
                }

                bool matchingPasswords_stfU = ComparePasswords_stfUpdate(StaffUpdateIn);
                if (!matchingPasswords_stfU)
                {
                    return;
                }

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                StaffUpdate stfU = StaffUpdateIn;

                bool valid = ValidatePassword_StfUpdate(StaffUpdateIn); // checks that password is validated

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("UPDATE person p JOIN staff s ON p.userID = s.userID " +
                     "SET p.firstName = '" + stfU.stfFNameTxt.Text +
                     "', p.surname = '" + stfU.stfSurnameTxt.Text + "', p.aLine1 = '" + stfU.stfALine1Txt.Text +
                     "', p.aLine2 = '" + stfU.stfALine2Txt.Text + "', p.aCity = '" + stfU.stfCityTxt.Text +
                     "', p.aCounty = '" + stfU.stfCountyTxt.Text + "', p.aPostcode = '" + stfU.stfPostcodeTxt.Text +
                     "', p.telNo = '" + stfU.stfTelNoTxt.Text + "', p.email = '" + stfU.stfEmailTxt.Text +
                     "', p.password = '" + stfU.stfPasswordUpdateTxt.Password + "', s.jobTitle = '" + stfU.stfJobTitleTxt.Text +
                     "' WHERE p.userID = " + stfU.stfIDUpdateCmbx.SelectedItem);

                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        stfU.stfUpdatedLblGrid.Visibility = Visibility.Visible;
                        stfU.stfUpdatedLbl.Content = ("Staff Member: " + StaffUpdateIn.stfIDUpdateCmbx.SelectedItem + " updated.");
                    }

                    else
                    {
                        con.Close();
                        MessageBox.Show("Staff member not updated");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static bool IsValidStfEmail_Update(StaffUpdate StaffUpdateIn)
        {
            string email = StaffUpdateIn.stfEmailTxt.Text;
            Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
            Match match = regex.Match(email);

            if (match.Success)
            {
                StaffUpdateIn.stfEmailValidationLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.stfEmailValidationLbl.Content = "Valid";
                StaffUpdateIn.stfEmailValidationLbl.Foreground = Brushes.Green;
                return true;
            }
            else
            {
                StaffUpdateIn.stfEmailValidationLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.stfEmailValidationLbl.Content = "Not Valid";
                StaffUpdateIn.stfEmailValidationLbl.Foreground = Brushes.Red;
                return false;
            }
        }

        public static bool ValidatePassword_StfUpdate(StaffUpdate StaffUpdateIn)
        {
            const int MIN_LENGTH = 8;
            const int MAX_LENGTH = 15;

            if (StaffUpdateIn.stfPasswordUpdateTxt.Password == "") throw new ArgumentNullException();

            bool meetsLengthRequirements = StaffUpdateIn.stfPasswordUpdateTxt.Password.Length >= MIN_LENGTH && StaffUpdateIn.stfPasswordUpdateTxt.Password.Length <= MAX_LENGTH;
            bool hasUpperCaseLetter = false;
            bool hasLowerCaseLetter = false;
            bool hasDecimalDigit = false;

            if (meetsLengthRequirements)
            {
                foreach (char c in StaffUpdateIn.stfPasswordUpdateTxt.Password)
                {
                    if (char.IsUpper(c)) hasUpperCaseLetter = true;
                    else if (char.IsLower(c)) hasLowerCaseLetter = true;
                    else if (char.IsDigit(c)) hasDecimalDigit = true;
                }
            }

            bool isValid = meetsLengthRequirements
                            && hasUpperCaseLetter
                            && hasLowerCaseLetter
                            && hasDecimalDigit;

            if (isValid == true)
            {
                StaffUpdateIn.validStfPasswordLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.validStfPasswordLbl.Content = ("Password valid");
                StaffUpdateIn.validStfPasswordLbl.Foreground = Brushes.Green;
            }
            else
            {
                StaffUpdateIn.validStfPasswordLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.validStfPasswordLbl.Content = ("Password not valid");
                StaffUpdateIn.validStfPasswordLbl.Foreground = Brushes.Red;
            }

            return isValid;
        }

        public static bool ComparePasswords_stfUpdate(StaffUpdate StaffUpdateIn)
        {
            if (StaffUpdateIn.stfPasswordUpdateTxt.Password == StaffUpdateIn.stfPassword1UpdateTxt.Password)
            {
                StaffUpdateIn.matchStfPasswordLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.matchStfPasswordLbl.Content = ("Passwords match");
                StaffUpdateIn.matchStfPasswordLbl.Foreground = Brushes.Green;
            }

            if (StaffUpdateIn.stfPasswordUpdateTxt.Password != StaffUpdateIn.stfPassword1UpdateTxt.Password)
            {
                StaffUpdateIn.matchStfPasswordLbl.Visibility = Visibility.Visible;
                StaffUpdateIn.matchStfPasswordLbl.Content = ("Passwords do not match");
                StaffUpdateIn.matchStfPasswordLbl.Foreground = Brushes.Red;
            }

            return String.Compare(StaffUpdateIn.stfPasswordUpdateTxt.Password, StaffUpdateIn.stfPassword1UpdateTxt.Password, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
        }

        // *-*-*-* Staff Delete Methods *-*-*-*

        public static void populateStfDeleteFields(StaffDelete StaffDeleteIn)
        {
            try
            {
                StaffDelete stfD = StaffDeleteIn;
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person JOIN staff ON person.userID = staff.userID " +
                "WHERE person.userID = '" + stfD.stfIDDeleteCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    stfD.stfFNameTxt.Text = dt.Rows[0]["firstName"].ToString();
                    stfD.stfSurnameTxt.Text = dt.Rows[0]["surname"].ToString();
                    stfD.stfJobTitleTxt.Text = dt.Rows[0]["jobTitle"].ToString();
                    stfD.stfTelNoTxt.Text = dt.Rows[0]["telNo"].ToString();
                    stfD.stfEmailTxt.Text = dt.Rows[0]["email"].ToString();
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteStaff(StaffDelete StaffDeleteIn)
        {
            try
            {
                StaffDelete stfD = StaffDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM person WHERE userID = " + stfD.stfIDDeleteCmbx.SelectedItem +
                        "; DELETE FROM staff WHERE userID = " + stfD.stfIDDeleteCmbx.SelectedItem + ";");
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        stfD.stfDeletedLblGrid.Visibility = Visibility.Visible;
                        stfD.stfDeletedLbl.Content = ("Staff Member: " + stfD.stfIDDeleteCmbx.SelectedItem + " Deleted");

                        stfD.stfIDDeleteCmbx.Text = "";
                        stfD.stfFNameTxt.Text = "";
                        stfD.stfSurnameTxt.Text = "";
                        stfD.stfJobTitleTxt.Text = "";
                        stfD.stfTelNoTxt.Text = "";
                        stfD.stfEmailTxt.Text = "";
                        FindStaffIDDelete(StaffDeleteIn);
                    }
                    if (a == 0)
                    {
                        con.Close();
                        stfD.stfDeletedLbl.Content = ("Staff member not deleted");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void FindStaffIDDelete(StaffDelete StaffDeleteIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    StaffDeleteIn.stfIDDeleteCmbx.Items.Add(dt.Rows[i]["staffID"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // *************************************************** STUDENT ***************************************************

        // *-*-*-* Student View Methods *-*-*-*

        public static void populateStudentListview(StudentView StudentViewIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM person p JOIN student s ON p.userid = s.userID");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "stdtPerson");

                    foreach (DataRow myRow in ds.Tables["stdtPerson"].Rows)
                    {
                        StudentViewIn.displayStdtDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Student Add Methods *-*-*-*

        public static void CreateStudent(StudentAdd StudentAddIn)
        {
            try
            {
                string nextStdtID = NextStudentID();

                StudentAdd stdtA = StudentAddIn;
                
                bool validPassword = ValidatePassword_StdtAdd(StudentAddIn); // checks that password is validated
                if (!validPassword)
                {
                    return;
                }

                bool validEmail = IsValidStdtEmail_Add(StudentAddIn);
                if (!validEmail)
                {
                    return;
                }
                
                bool matchingPassword_stdtA = ComparePasswords_stdtAdd(StudentAddIn);
                if (!matchingPassword_stdtA)
                {
                    return;
                }
                bool validPostcode = isValidPostcode(StudentAddIn);
                if (!validPostcode)
                {
                    return;
                }

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT person (userID, firstName, surname, aLine1, aLine2, aCity, "+ 
                        "aCounty, aPostcode, telNo, email, password)" +
                        "VALUES ('" + nextStdtID + "', '" + stdtA.stdtFNameTxt.Text + "', '" + stdtA.stdtSurnameTxt.Text + "', '" +
                        stdtA.stdtALine1Txt.Text + "', '" + stdtA.stdtALine2Txt.Text + "', '" + stdtA.stdtCityTxt.Text +
                        "', '" + stdtA.stdtCountyTxt.Text + "', '" + stdtA.stdtPostcodeTxt.Text + "', '" +
                        stdtA.stdtTelNoTxt.Text + "', '" + stdtA.stdtEmailTxt.Text + "', '" + stdtA.stdtPwrd1AddTxt.Password + "');");
                    int a = cmd.ExecuteNonQuery();
                    
                    cmd.CommandText = string.Format("INSERT student (courseID, studentPic, userID) " +
                        "VALUES ('" + stdtA.stdtCourseIDLbl.Content + "', '" + stdtA.stdtPicTxt.Text + "', '" + nextStdtID + "');");
                    int b = cmd.ExecuteNonQuery();

                    if (a >= 1 && b != 0)
                    {
                        con.Close();
                        stdtA.stdtAddedLblGrid.Visibility = Visibility.Visible;
                        stdtA.stdtAddedLbl.Content = ("Student: " + stdtA.stdtIDAddTxt.Text + " added");
                        stdtA.stdtFNameTxt.Text = "";
                        stdtA.stdtSurnameTxt.Text = "";
                        stdtA.stdtALine1Txt.Text = "";
                        stdtA.stdtALine2Txt.Text = "";
                        stdtA.stdtCityTxt.Text = "";
                        stdtA.stdtCountyTxt.Text = "";
                        stdtA.stdtPostcodeTxt.Text = "";
                        stdtA.stdtTelNoTxt.Text = "";
                        stdtA.stdtEmailTxt.Text = "";
                        stdtA.stdtPicTxt.Text = "";
                        stdtA.stdtCourseTitleCmbx.Text = "";
                        stdtA.stdtCourseIDLbl.Content = "";
                        stdtA.stdtIDAddTxt.Text = NextStudentID();
                        stdtA.stdtPwrdAddTxt.Password = "";
                        stdtA.stdtPwrd1AddTxt.Password = "";

                        stdtA.stdtEmailValidationLbl.Visibility = Visibility.Hidden;
                        stdtA.validStdtPasswordLbl.Visibility = Visibility.Hidden;
                        stdtA.matchStdtPasswordLbl.Visibility = Visibility.Hidden;

                        WebBrowser hiddenWeb = new WebBrowser();
                        hiddenWeb.Navigate("http://localhost/Framework_Tool/email.php?address=" + stdtA.stdtEmailTxt.Text);
                    }

                    else
                    {
                        con.Close();
                        stdtA.stdtAddedLbl.Content = ("Student: " + stdtA.stdtIDAddTxt.Text + " not added");
                    }
                }
            }

            catch (Exception ex)
            {
                
                con.Close();

                StudentAddIn.stdtAddedLbl.Content = ("Student: " + StudentAddIn.stdtIDAddTxt.Text + " not added");
                MessageBox.Show(ex.Message);
            }
        }

        public static string NextStudentID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }

            string nextStudentID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'student'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextStudentID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextStudentID;
        }

        public static void populateCourseID_Add(StudentAdd StudentAddIn)
        {
            try
            {
                StudentAdd stdtA = StudentAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course " +
                "WHERE courseTitle = '" + stdtA.stdtCourseTitleCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    stdtA.courseID = Convert.ToInt32(dt.Rows[0]["courseID"].ToString());
                    stdtA.stdtCourseIDLbl.Content = dt.Rows[0]["courseID"].ToString();
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void FindStdtCourseTitle_Add(StudentAdd StudentAddIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    StudentAddIn.stdtCourseTitleCmbx.Items.Add(dt.Rows[i]["courseTitle"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static bool IsValidStdtEmail_Add(StudentAdd StudentAddIn)
        {
            string email = StudentAddIn.stdtEmailTxt.Text;
            Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
            Match match = regex.Match(email);

            if (match.Success)
            {
                StudentAddIn.stdtEmailValidationLbl.Visibility = Visibility.Visible;
                StudentAddIn.stdtEmailValidationLbl.Content = "Valid email";
                StudentAddIn.stdtEmailValidationLbl.Foreground = Brushes.Green;
                return true;
            }
            else
            {
                StudentAddIn.stdtEmailValidationLbl.Visibility = Visibility.Visible;
                StudentAddIn.stdtEmailValidationLbl.Content = "Not a valid email";
                StudentAddIn.stdtEmailValidationLbl.Foreground = Brushes.Red;
                return false;
            }
        }

        public static bool isValidPostcode(StudentAdd StdtAddIn)
        {
            string ukPostCode = StdtAddIn.stdtPostcodeTxt.Text;
            string UK_POST_PATTERN = @"^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$";
            Regex ukPostRegex = new Regex(UK_POST_PATTERN, RegexOptions.Compiled);
            Match match = ukPostRegex.Match(ukPostCode);
            if (match.Success)
            {
                StdtAddIn.validPostcodeLbl.Visibility = Visibility.Visible;
                StdtAddIn.validPostcodeLbl.Content = "Valid Postcode";
                StdtAddIn.validPostcodeLbl.Foreground = Brushes.Green;
                return true;
            }
            else
            {
                StdtAddIn.validPostcodeLbl.Visibility = Visibility.Visible;
                StdtAddIn.validPostcodeLbl.Content = "Invalid Postcode";
                StdtAddIn.validPostcodeLbl.Foreground = Brushes.Red;
                return false;
            }
        }

        public static bool ValidatePassword_StdtAdd(StudentAdd StudentAddIn)
        {
            const int MIN_LENGTH = 8;
            const int MAX_LENGTH = 15;

            if (StudentAddIn.stdtPwrdAddTxt.Password == "") throw new ArgumentNullException();

            bool meetsLengthRequirements = StudentAddIn.stdtPwrdAddTxt.Password.Length >= MIN_LENGTH && StudentAddIn.stdtPwrdAddTxt.Password.Length <= MAX_LENGTH;
            bool hasUpperCaseLetter = false;
            bool hasLowerCaseLetter = false;
            bool hasDecimalDigit = false;

            if (meetsLengthRequirements)
            {
                foreach (char c in StudentAddIn.stdtPwrdAddTxt.Password)
                {
                    if (char.IsUpper(c)) hasUpperCaseLetter = true;
                    else if (char.IsLower(c)) hasLowerCaseLetter = true;
                    else if (char.IsDigit(c)) hasDecimalDigit = true;

                }
            }

            bool isValid = meetsLengthRequirements
                            && hasUpperCaseLetter
                            && hasLowerCaseLetter
                            && hasDecimalDigit;

            if (isValid == true)
            {
                StudentAddIn.validStdtPasswordLbl.Visibility = Visibility.Visible;
                StudentAddIn.validStdtPasswordLbl.Content = ("Valid password");
                StudentAddIn.validStdtPasswordLbl.Foreground = Brushes.Green;
            }
            else
            {
                StudentAddIn.validStdtPasswordLbl.Visibility = Visibility.Visible;
                StudentAddIn.validStdtPasswordLbl.Content = ("Not a valid password");
                StudentAddIn.validStdtPasswordLbl.Foreground = Brushes.Red;
            }

            return isValid;
        }

        public static bool ComparePasswords_stdtAdd(StudentAdd StudentAddIn)
        {
            if (StudentAddIn.stdtPwrdAddTxt.Password == StudentAddIn.stdtPwrd1AddTxt.Password)
            {
                StudentAddIn.matchStdtPasswordLbl.Visibility = Visibility.Visible;
                StudentAddIn.matchStdtPasswordLbl.Content = ("Passwords match");
                StudentAddIn.matchStdtPasswordLbl.Foreground = Brushes.Green;
            }

            else
            {
                StudentAddIn.matchStdtPasswordLbl.Visibility = Visibility.Visible;
                StudentAddIn.matchStdtPasswordLbl.Content = ("Passwords do not match");
                StudentAddIn.matchStdtPasswordLbl.Foreground = Brushes.Red;
            }

            return String.Compare(StudentAddIn.stdtPwrdAddTxt.Password, StudentAddIn.stdtPwrd1AddTxt.Password, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
        }

        // *-*-*-* Student Delete Methods *-*-*-*

        public static void FindStdtID_Delete(StudentDelete StdtDeleteIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM student", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    StdtDeleteIn.stdtIDDeleteCmbx.Items.Add(dt.Rows[i]["studentID"]);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void populateStdtDeleteFields(StudentDelete StdtDeleteIn)
        {
            try
            {
                StudentDelete stdtD = StdtDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person p JOIN student s ON p.userID = s.userID " +
                "WHERE p.userID = '" + stdtD.stdtIDDeleteCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    con.Close();
                    stdtD.stdtFNameTxt.Text = dt.Rows[0]["firstName"].ToString();
                    stdtD.stdtSurnameTxt.Text = dt.Rows[0]["surname"].ToString();
                    stdtD.stdtCourseIDTxt.Text = dt.Rows[0]["courseID"].ToString();
                    stdtD.stdtTelNoTxt.Text = dt.Rows[0]["telNo"].ToString();
                    stdtD.stdtEmailTxt.Text = dt.Rows[0]["email"].ToString();
                }
                else
                {
                    con.Close();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteStudent(StudentDelete StudentDeleteIn)
        {
            try
            {
                StudentDelete stdtD = StudentDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM student WHERE studentID = '" 
                        + stdtD.stdtIDDeleteCmbx.SelectedItem + "'; DELETE FROM person WHERE userID = '" 
                        + stdtD.stdtIDDeleteCmbx.SelectedItem + "';");
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        stdtD.stdtDeletedLblGrid.Visibility = Visibility.Visible;
                        stdtD.stdtDeletedLbl.Content = ("Student: " + stdtD.stdtIDDeleteCmbx.SelectedItem + " deleted.");
                        stdtD.stdtIDDeleteCmbx.Text = "";
                        stdtD.stdtFNameTxt.Text = "";
                        stdtD.stdtSurnameTxt.Text = "";
                        stdtD.stdtCourseIDTxt.Text = "";
                        stdtD.stdtTelNoTxt.Text = "";
                        stdtD.stdtEmailTxt.Text = "";
                    }

                    else
                    {
                        con.Close();
                        stdtD.stdtDeletedLbl.Content = ("Student: " + stdtD.stdtIDDeleteCmbx.SelectedItem +" not deleted.");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Student Update Methods *-*-*-*

        public static void FindStudentID_Update(StudentUpdate StudentUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM student", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    StudentUpdateIn.stdtIDUpdateCmbx.Items.Add(dt.Rows[i]["studentID"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_StdtUpdate(StudentUpdate StudentUpdateIn)
        {
            try
            {
                StudentUpdate stdtU = StudentUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person JOIN (student JOIN course ON student.courseID = course.courseID) ON person.userID = student.userID "
                    + "WHERE person.userID = '" + stdtU.stdtIDUpdateCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    con.Close();
                    stdtU.stdtFNameTxt.Text = dt.Rows[0]["firstName"].ToString();
                    stdtU.stdtSurnameTxt.Text = dt.Rows[0]["surname"].ToString();
                    stdtU.stdtALine1Txt.Text = dt.Rows[0]["aLine1"].ToString();
                    stdtU.stdtALine2Txt.Text = dt.Rows[0]["aLine2"].ToString();
                    stdtU.stdtCityTxt.Text = dt.Rows[0]["aCity"].ToString();
                    stdtU.stdtCountyTxt.Text = dt.Rows[0]["aCounty"].ToString();
                    stdtU.stdtPostcodeTxt.Text = dt.Rows[0]["aPostcode"].ToString();
                    stdtU.stdtTelNoTxt.Text = dt.Rows[0]["telNo"].ToString();
                    stdtU.stdtEmailTxt.Text = dt.Rows[0]["email"].ToString();
                    stdtU.stdtPicTxt.Text = dt.Rows[0]["studentPic"].ToString();
                    stdtU.stdtPasswordUpdateTxt.Password = dt.Rows[0]["password"].ToString();
                    stdtU.stdtPassword1UpdateTxt.Password = dt.Rows[0]["password"].ToString();

                    stdtU.stdtCourseTitleCmbx.SelectedItem = dt.Rows[0]["courseTitle"].ToString();
                    stdtU.stdtCourseIDLbl.Content = dt.Rows[0]["courseID"].ToString();
                    stdtU.stdtImage_update.Source = new BitmapImage(new Uri(stdtU.stdtPicTxt.Text));
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void UpdateStudent(StudentUpdate StudentUpdateIn)
        {
            try
            {
                bool validEmail = IsValidStdtEmail_Update(StudentUpdateIn);
                if (!validEmail)
                {
                    return;
                }

                bool validPassword = ValidatePassword_StdtUpdate(StudentUpdateIn); // checks that password is validated
                if (!validPassword)
                {
                    return;
                }

                bool matchingPasswords_stdtA = ComparePasswords_stdtUpdate(StudentUpdateIn);
                if (!matchingPasswords_stdtA)
                {
                    return;
                }

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                StudentUpdate stdtU = StudentUpdateIn;

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("UPDATE person p JOIN student s ON p.userID = s.userID " +
                     "SET p.firstName = '" + stdtU.stdtFNameTxt.Text +
                     "', p.surname = '" + stdtU.stdtSurnameTxt.Text + "', p.aLine1 = '" + stdtU.stdtALine1Txt.Text +
                     "', p.aLine2 = '" + stdtU.stdtALine2Txt.Text + "', p.aCity = '" + stdtU.stdtCityTxt.Text +
                     "', p.aCounty = '" + stdtU.stdtCountyTxt.Text + "', p.aPostcode = '" + stdtU.stdtPostcodeTxt.Text +
                     "', p.telNo = '" + stdtU.stdtTelNoTxt.Text + "', p.email = '" + stdtU.stdtEmailTxt.Text +
                     "', p.password = '" + stdtU.stdtPasswordUpdateTxt.Password + "', s.courseID = '" + stdtU.stdtCourseIDLbl.Content +
                     "', s.studentPic = '" + stdtU.stdtPicTxt.Text + "' WHERE p.userID = " + stdtU.stdtIDUpdateCmbx.SelectedItem);
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        stdtU.stdtUpdatedLblGrid.Visibility = Visibility.Visible;
                        stdtU.stdtUpdatedLbl.Content = ("Student: " + stdtU.stdtIDUpdateCmbx.SelectedItem + " updated");
                    }

                        else
                    {
                        con.Close();
                        MessageBox.Show("Student not updated");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static bool IsValidStdtEmail_Update(StudentUpdate StdtUpdateIn)
        {
            string email = StdtUpdateIn.stdtEmailTxt.Text;
            Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
            Match match = regex.Match(email);

            if (match.Success)
            {
                StdtUpdateIn.stdtEmailValidationLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.stdtEmailValidationLbl.Content = "Valid";
                StdtUpdateIn.stdtEmailValidationLbl.Foreground = Brushes.Green;
                return true;
            }
            else
            {
                StdtUpdateIn.stdtEmailValidationLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.stdtEmailValidationLbl.Content = "Not Valid";
                StdtUpdateIn.stdtEmailValidationLbl.Foreground = Brushes.Red;
                return false;
            }
        }

        public static bool ValidatePassword_StdtUpdate(StudentUpdate StdtUpdateIn)
        {
            const int MIN_LENGTH = 8;
            const int MAX_LENGTH = 15;

            if (StdtUpdateIn.stdtPasswordUpdateTxt.Password == "") throw new ArgumentNullException();

            bool meetsLengthRequirements = StdtUpdateIn.stdtPasswordUpdateTxt.Password.Length >= MIN_LENGTH && StdtUpdateIn.stdtPasswordUpdateTxt.Password.Length <= MAX_LENGTH;
            bool hasUpperCaseLetter = false;
            bool hasLowerCaseLetter = false;
            bool hasDecimalDigit = false;

            if (meetsLengthRequirements)
            {
                foreach (char c in StdtUpdateIn.stdtPasswordUpdateTxt.Password)
                {
                    if (char.IsUpper(c)) hasUpperCaseLetter = true;
                    else if (char.IsLower(c)) hasLowerCaseLetter = true;
                    else if (char.IsDigit(c)) hasDecimalDigit = true;

                }
            }

            bool isValid = meetsLengthRequirements
                            && hasUpperCaseLetter
                            && hasLowerCaseLetter
                            && hasDecimalDigit;

            if (isValid == true)
            {
                StdtUpdateIn.validStdtPasswordLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.validStdtPasswordLbl.Content = ("Password valid");
                StdtUpdateIn.validStdtPasswordLbl.Foreground = Brushes.Green;
            }
            else
            {
                StdtUpdateIn.validStdtPasswordLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.validStdtPasswordLbl.Content = ("Password not valid");
                StdtUpdateIn.validStdtPasswordLbl.Foreground = Brushes.Red;
            }

            return isValid;
        }

        public static bool ComparePasswords_stdtUpdate(StudentUpdate StdtUpdateIn)
        {
            if (StdtUpdateIn.stdtPasswordUpdateTxt.Password == StdtUpdateIn.stdtPassword1UpdateTxt.Password)
            {
                StdtUpdateIn.matchStdtPasswordLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.matchStdtPasswordLbl.Content = ("Passwords match");
                StdtUpdateIn.matchStdtPasswordLbl.Foreground = Brushes.Green;
            }

            else
            {
                StdtUpdateIn.matchStdtPasswordLbl.Visibility = Visibility.Visible;
                StdtUpdateIn.matchStdtPasswordLbl.Content = ("Passwords do not match");
                StdtUpdateIn.matchStdtPasswordLbl.Foreground = Brushes.Red;
            }

            return String.Compare(StdtUpdateIn.stdtPasswordUpdateTxt.Password, StdtUpdateIn.stdtPassword1UpdateTxt.Password, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
        }

        public static void FindStdtcourseTitle_Update(StudentUpdate StdtUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                
                con.Close();
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    
                    StdtUpdateIn.stdtCourseTitleCmbx.Items.Add(dt.Rows[i]["courseTitle"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateCourseID_Update(StudentUpdate StudentUpdateIn)
        {
            try
            {
                StudentUpdate stdtU = StudentUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course " +
                "WHERE courseTitle = '" + stdtU.stdtCourseTitleCmbx.SelectedItem + "'", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                con.Close();

                if (dt.Rows.Count > 0)
                {
                    StudentUpdateIn.stdtCourseIDLbl.Content = dt.Rows[0]["courseID"].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }
        
        // *-*-*-* Student Search Methods *-*-*-*

        // *************************************************** COUSRE ***************************************************
    
        // *-*-*-* Course View Methods *-*-*-*

        public static void populateCrseListview(CourseView CrseViewIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT c.courseID, c.courseLevel, c.courseTitle, p.firstName, "
                        + "p.surname FROM course c JOIN (staff s JOIN person p " +
                        "ON s.staffID = p.userID) ON c.courseLeaderStaffID = s.staffID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "crse");

                    foreach (DataRow myRow in ds.Tables["crse"].Rows)
                    {
                        CrseViewIn.displayCrseDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Course Add Methods *-*-*-*
        
        public static string NextCrseID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }

            string nextCrseID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'course'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextCrseID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextCrseID;
        }

        public static void CreateCourse(CourseAdd CourseAddIn)
        {
            CourseAdd cA = CourseAddIn;

            try
            {
                string nextCrseID = NextCrseID();
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT course (courseID, courseLevel, courseTitle, preRequisites, courseLeaderStaffID) " +
                        "VALUES ('" + nextCrseID + "', '" + cA.crseLevelTxt.Text + "', '" 
                        + cA.crseTitleTxt.Text + "', '" + cA.crsePreReqTxt.Text + "', '" + cA.crseLeaderIDLbl.Content + "');");
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        cA.crseAddedLblGrid.Visibility = Visibility.Visible;
                        cA.crseAddedLbl.Content = ("Course: " + cA.crseIDTxt.Text + " added");
                        cA.crseIDTxt.Text = NextCrseID();
                        cA.crseTitleTxt.Text = "";
                        cA.crseLevelTxt.Text = "";
                        cA.crseLeaderNameCmbx_Add.Text = "";
                        cA.crseLeaderIDLbl.Content = "";
                        cA.crsePreReqTxt.Text = "";
                    }

                    else
                    {
                        con.Close();
                        cA.crseAddedLbl.Content = ("Course: " + cA.crseIDTxt.Text + " not added");
                    }
                }
            }

            catch (Exception ex)
            {

                con.Close();

                cA.crseAddedLbl.Content = ("Course: " + cA.crseIDTxt.Text + " not added");
                MessageBox.Show(ex.Message);
            }
        }

        public static void FindStfName_CrseAdd(CourseAdd CourseAddIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff s JOIN person p ON s.userID = p.userID;", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                con.Close();

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    CourseAddIn.crseLeaderNameCmbx_Add.Items.Add(dt.Rows[i]["firstName"] + " " + dt.Rows[i]["surname"]);
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateStfID_CrseAdd(CourseAdd CourseAddIn)
        {
            try
            {
                CourseAdd cA = CourseAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                string row = Convert.ToString(cA.crseLeaderNameCmbx_Add.SelectedItem);
                int x = row.IndexOf(' ');
                int rowLength = row.Length;
                int y = rowLength - (x + 1);
                int z = x + 1;

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT s.staffID, p.firstName, p.surname FROM staff s JOIN person p ON s.userID = p.userID " +
                    "WHERE firstName ='" + cA.crseLeaderNameCmbx_Add.SelectedItem.ToString().Substring(0, x) + 
                    "' AND surname ='" + cA.crseLeaderNameCmbx_Add.SelectedItem.ToString().Substring(z, y) + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                con.Close();

                if (dt.Rows.Count > 0)
                {
                    cA.crseLeaderIDLbl.Content = dt.Rows[0]["staffID"];
                }
                else
                {
                    cA.crseLeaderIDLbl.Content = "";
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Course Update Methods *-*-*-*

        public static void FindCourseID_Update(CourseUpdate CourseUpdateIn)
        {
            try
            {
                CourseUpdate cu = CourseUpdateIn;
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    cu.crseIDCmbx.Items.Add(dt.Rows[i]["courseID"]);
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_CrseUpdate(CourseUpdate CrseUpdateIn)
        {
            try
            {
                CourseUpdate cu = CrseUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person p JOIN (staff s JOIN course c ON " + 
                    "s.staffID = c.courseLeaderStaffID) ON p.userID = s.userID WHERE c.courseID = '" + cu.crseIDCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    con.Close();
                    cu.crseTitleTxt.Text = dt.Rows[0]["courseTitle"].ToString();
                    cu.crseLevelTxt.Text = dt.Rows[0]["courseLevel"].ToString();
                    cu.crseLeaderNameCmbx_Update.SelectedItem = dt.Rows[0]["firstName"] + " " + dt.Rows[0]["surname"];
                    cu.crseLeaderIDLbl.Content = dt.Rows[0]["courseLeaderStaffID"].ToString();
                    cu.crsePreReqTxt.Text = dt.Rows[0]["preRequisites"].ToString();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void FindStfName_CrseUpdate(CourseUpdate CourseUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                /* MySqlDataAdapter da = new MySqlDataAdapter("SELECT p.firstName, p.surname FROM staff s" + 
                    " JOIN person p ON s.userID = p.userID;", con);
                DataTable dt = new DataTable();
                */

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff s JOIN person p ON s.userID = p.userID;", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                con.Close();

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    CourseUpdateIn.crseLeaderNameCmbx_Update.Items.Add(dt.Rows[i]["firstName"] + " " + dt.Rows[i]["surname"]);
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateCrseLeaderID_CrseUpdate(CourseUpdate CourseUpdateIn)
        {
            try
            {
                CourseUpdate cu = CourseUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                String row = cu.crseLeaderNameCmbx_Update.SelectedValue.ToString();
                int x = row.IndexOf(' ');
                int rowLength = cu.crseLeaderNameCmbx_Update.SelectedValue.ToString().Length;
                int y = rowLength - (x + 1);
                int z = x + 1;

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT s.staffID, p.firstName, p.surname FROM staff s JOIN person p ON s.userID = p.userID " +
                    "WHERE firstName ='" + cu.crseLeaderNameCmbx_Update.SelectedItem.ToString().Substring(0, x) +
                    "' AND surname ='" + cu.crseLeaderNameCmbx_Update.SelectedItem.ToString().Substring(z, y) + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                con.Close();

                if (dt.Rows.Count > 0)
                {
                    cu.crseLeaderIDLbl.Content = dt.Rows[0]["staffID"];
                }
                else
                {
                    cu.crseLeaderIDLbl.Content = "";
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void UpdateCourse(CourseUpdate CourseUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                CourseUpdate cu = CourseUpdateIn;

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("UPDATE course " +
                     "SET courseID = '" + cu.crseIDCmbx.SelectedItem + "', courseLevel = '" + cu.crseLevelTxt.Text + 
                     "', courseTitle = '" + cu.crseTitleTxt.Text + "', preRequisites = '" + cu.crsePreReqTxt.Text +
                     "', courseLeaderStaffID = '" + cu.crseLeaderIDLbl.Content + "' WHERE courseID = " + cu.crseIDCmbx.SelectedItem);
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        cu.crseUpdatedLblGrid.Visibility = Visibility.Visible;
                        cu.crseUpdatedLbl.Content = ("Course: " + cu.crseIDCmbx.SelectedItem + " updated");
                    }

                    else
                    {
                        con.Close();
                        cu.crseUpdatedLbl.Content = ("Course not updated");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }


        // *-*-*-* Course Delete Methods *-*-*-*

        public static void FindCourseID_Delete(CourseDelete CourseDeleteIn)
        {
            try
            {
                CourseDelete cd = CourseDeleteIn;
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);
                
                con.Close();
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {    
                    cd.crseIDCmbx.Items.Add(dt.Rows[i]["courseID"]);
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_CrseDelete(CourseDelete CrseDeleteIn)
        {
            try
            {
                CourseDelete cd = CrseDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM person p JOIN (staff s JOIN course c ON " +
                    "s.staffID = c.courseLeaderStaffID) ON p.userID = s.userID WHERE c.courseID = '" + cd.crseIDCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    con.Close();
                    cd.crseTitleTxt.Text = dt.Rows[0]["courseTitle"].ToString();
                    cd.crseLevelTxt.Text = dt.Rows[0]["courseLevel"].ToString();
                    cd.crseLeaderNameTxt_Delete.Text = dt.Rows[0]["firstName"] + " " + dt.Rows[0]["surname"];
                    cd.crseLeaderIDLbl.Content = dt.Rows[0]["courseLeaderStaffID"].ToString();
                    cd.crsePreReqTxt.Text = dt.Rows[0]["preRequisites"].ToString();
                }
                else
                {
                    con.Close();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteCourse(CourseDelete CourseDeleteIn)
        {
            try
            {
                CourseDelete cd = CourseDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM course WHERE courseID = '"
                        + cd.crseIDCmbx.SelectedItem + "';");
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        cd.crseDeletedLblGrid.Visibility = Visibility.Visible;
                        cd.crseDeletedLbl.Content = ("Course: " + cd.crseIDCmbx.SelectedItem + " deleted");
                        cd.crseIDCmbx.Text = "";
                        cd.crseTitleTxt.Text = "";
                        cd.crseLevelTxt.Text = "";
                        cd.crseLeaderNameTxt_Delete.Text = "";
                        cd.crseLeaderIDLbl.Content = "";
                        cd.crsePreReqTxt.Text = "";
                    }

                    else
                    {
                        con.Close();
                        cd.crseDeletedLblGrid.Visibility = Visibility.Visible;
                        cd.crseDeletedLbl.Content = ("Course: " + cd.crseIDCmbx.SelectedItem + " not deleted");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Course Search Methods *-*-*-*

        // *************************************************** UNIT ***************************************************

        // *-*-*-* Unit View Methods *-*-*-*
        public static void populateUnitListview(UnitView UnitViewIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT u.unitID, u.unitTitle, u.description, p.firstName, p.surname " +
                        "FROM unit u JOIN (teaches t JOIN (staff s JOIN person p ON s.staffID = p.userID) " +
                    "ON t.staffID = s.staffID) ON u.unitID = t.unitID GROUP BY u.unitID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "unitID");

                    foreach (DataRow myRow in ds.Tables["unitID"].Rows)
                    {
                        UnitViewIn.displayUnitDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        
        // *-*-*-* Unit Add Methods *-*-*-*

        public static void CreateUnit(UnitAdd UnitAddIn)
        {
            try
            {
                string nextUnitID = NextUnitID();

                UnitAdd ua = UnitAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT unit (unitID, unitTitle, description, courseID) " +
                        "VALUES ('" + nextUnitID + "', '" + ua.unitTitleTxt.Text + "', '" + ua.unitDescriptionTxt.Text + 
                        "', '" + ua.crseIDAddLbl.Content + "');");
                    int a = cmd.ExecuteNonQuery();
                    
                    for (int i = 0; i < ua.unitStfListBox.Items.Count; i++)
                    {
                        string currentValue = Convert.ToString(ua.unitStfListBox.Items[i]);
                        
                        cmd.CommandText = string.Format("INSERT teaches (staffID, unitID) " +
                       "VALUES ('" + currentValue + "', '" + nextUnitID + "');");
                        int b = cmd.ExecuteNonQuery();
                    }

                    if (a != 0)
                    {
                        con.Close();
                        ua.unitAddedLblGrid.Visibility = Visibility.Visible;
                        ua.unitAddedLbl.Content = "Unit: " + nextUnitID + " Added";

                        ua.unitIDAddTxt.Text = NextUnitID();
                        ua.unitAdd_CourseTitleCmbx.Text = "";
                        ua.unitAdd_CourseTitleCmbx.Text = "";
                        ua.crseIDAddLbl.Content = "";
                        ua.unitAdd_StfIDCmbx.Text = "";
                        ua.unitAdd_StfNameLbl.Content = "";
                        ua.unitStfListBox.Items.Clear();
                        ua.unitTitleTxt.Text = "";
                        ua.unitDescriptionTxt.Text = "";                        
                    }

                    else
                    {
                        con.Close();
                        ua.unitAddedLbl.Content = "Unit Not Added";
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static string NextUnitID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }

            string nextUnitID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'unit'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextUnitID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextUnitID;
        }

        public static void FindStfID_UnitAdd(UnitAdd UnitAddIn)
        {
            try
            {
                UnitAdd ua = UnitAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    UnitAddIn.unitAdd_StfIDCmbx.Items.Add(dt.Rows[i]["staffID"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateStfID_UnitAdd(UnitAdd UnitAddIn)
        {
            try
            {
                UnitAdd unitA = UnitAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff JOIN person ON staff.userID = person.userID " +
                "WHERE staff.staffID = '" + unitA.unitAdd_StfIDCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    UnitAddIn.unitAdd_StfNameLbl.Content = dt.Rows[0]["firstName"].ToString() + " " + dt.Rows[0]["surname"].ToString();
                }

                con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateStfTeachesList_UnitAdd(UnitAdd UnitAddIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM teaches");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "staffPerson");
                    ListBoxItem headings = new ListBoxItem();
                    headings.Content = "StaffID";
                    headings.IsEnabled = false;
                    UnitAddIn.unitStfListBox.Items.Add(headings);
                    foreach (DataRow myRow in ds.Tables["staffTeaches"].Rows)
                    {
                        UnitAddIn.unitStfListBox.Items.Add(myRow[1].ToString());
                    }

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void FindCrseTitle_UnitAdd(UnitAdd UnitAddIn)
        {
            try
            {
                UnitAdd ua = UnitAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    UnitAddIn.unitAdd_CourseTitleCmbx.Items.Add(dt.Rows[i]["courseTitle"]);
                }

                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateCourseID_UnitAdd(UnitAdd UnitAddIn)
        {
            try
            {
                UnitAdd unitA = UnitAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course " +
                "WHERE courseTitle = '" + unitA.unitAdd_CourseTitleCmbx.SelectedItem + "'", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    UnitAddIn.crseIDAddLbl.Content = dt.Rows[0]["courseID"].ToString();
                }

                con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Unit Update Methods *-*-*-*

        public static void FindUnitID_Update(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    UnitUpdateIn.unitIDUpdateCmbx.Items.Add(dt.Rows[i]["unitID"]);
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void FindCourseTitle_UnitUpdate(UnitUpdate UnitUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    UnitUpdateIn.unitUpdate_CourseTitleCmbx.Items.Add(dt.Rows[i]["courseTitle"]);
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateCourseID_UnitUpdate(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate unitU = UnitUpdateIn;
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course " +
                "WHERE courseTitle = '" + unitU.unitUpdate_CourseTitleCmbx.SelectedItem + "'", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    con.Close();
                    UnitUpdateIn.crseIDUpdateLbl.Content = dt.Rows[0]["courseID"].ToString();
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_UnitUpdate(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course c JOIN " + 
                    "(unit u JOIN teaches t ON u.unitID = t.unitID) ON c.courseID = u.courseID "
                    + "WHERE t.unitID = '" + uu.unitIDUpdateCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);

                uu.unitStfUpdateListBox.Items.Clear();
                uu.unitUpdate_CourseTitleCmbx.Text = "";
                uu.crseIDUpdateLbl.Content = "";
                uu.unitTitleUpdateTxt.Text = "";
                uu.unitUpdateDescTxt.Text = "";

                if (dt.Rows.Count > 0)
                {
                    //con.Close();
                    uu.unitUpdate_CourseTitleCmbx.SelectedItem = dt.Rows[0]["courseTitle"].ToString();
                    uu.crseIDUpdateLbl.Content = dt.Rows[0]["courseID"].ToString();

                    UnitUpdateIn.unitStfUpdateListBox.Items.Clear();
                    populateTeachingList_UnitUpdate(UnitUpdateIn);

                    uu.unitTitleUpdateTxt.Text = dt.Rows[0]["unitTitle"].ToString();
                    uu.unitUpdateDescTxt.Text = dt.Rows[0]["description"].ToString();
                }             
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteTeachesRow(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM teaches WHERE unitID ='" + 
                        uu.unitIDUpdateCmbx.SelectedItem + "' AND staffID = '" 
                        + uu.unitStfUpdateListBox.SelectedItem.ToString().Substring(0, 6) + "';");
                    int a = cmd.ExecuteNonQuery();
                    con.Close();

                    /*if (a != 0)
                    {
                        uu.unitStfUpdateListBox.Items.Remove(uu.unitStfUpdateListBox.SelectedItem);
                    }*/
                }
            }
            catch (Exception e)
            {
                con.Close();
                MessageBox.Show(e.Message);
            }
        }

        public static void UpdateUnit(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("UPDATE unit SET unitTitle = '" + uu.unitTitleUpdateTxt.Text +
                        "', description = '" + uu.unitUpdateDescTxt.Text + "', courseID = '" + uu.crseIDUpdateLbl.Content
                        + "' WHERE unitID = '" + uu.unitIDUpdateCmbx.SelectedItem + "';");
                    int a = cmd.ExecuteNonQuery();

                    // int x = uu.unitStfUpdateListBox.SelectedItem.ToString().IndexOf(' ');
                    for (int i = 0; i < uu.unitStfUpdateListBox.Items.Count; i++)
                    {
                        string currentValue = Convert.ToString(uu.unitStfUpdateListBox.Items[i].ToString().Substring(0, 6));

                        cmd.CommandText = string.Format("SELECT staffID, unitID FROM teaches WHERE staffID ='"
                            + currentValue + "' AND unitID ='" + uu.unitIDUpdateCmbx.SelectedItem.ToString() + "';");
                        int y = cmd.ExecuteNonQuery();

                        MySqlDataReader dr = cmd.ExecuteReader();
                        // while (dr.Read())
                        // {
                            if (dr.HasRows == true)
                            {
                                dr.Close();
                            }

                            else if (dr.HasRows == false)
                            {
                                dr.Close();
                                cmd.CommandText = string.Format("INSERT teaches (staffID, unitID) " +
                                    "VALUES ('" + currentValue + "', '" + uu.unitIDUpdateCmbx.SelectedItem + "');");
                                cmd.ExecuteNonQuery();
                            }
                        // }
                    }

                    con.Close();
                    uu.unitUpdatedLblGrid.Visibility = Visibility.Visible;
                    uu.unitUpdatedLbl.Content = "Unit " + uu.unitIDUpdateCmbx.Text + " Updated";
                    UnitUpdateIn.unitIDUpdateCmbx.Text = "";
                    UnitUpdateIn.unitTitleUpdateTxt.Text = "";
                    UnitUpdateIn.unitUpdateDescTxt.Text = "";
                    UnitUpdateIn.unitUpdate_CourseTitleCmbx.Text = "";
                    UnitUpdateIn.crseIDUpdateLbl.Content = "";
                    uu.unitUpdate_StfNameLbl.Content = "";
                    uu.unitUpdate_StfIDCmbx.Text = "";
                    uu.unitStfUpdateListBox.Items.Clear();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public static void FindStfID_UnitUpdate(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM staff", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    // con.Close();
                    UnitUpdateIn.unitUpdate_StfIDCmbx.Items.Add(dt.Rows[i]["staffID"].ToString());
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static String populateStfID_UnitUpdate(object id)
        {
            String name = null;
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataReader reader = new MySqlCommand("SELECT firstName, surname FROM staff JOIN person ON " +
                    "staff.userID = person.userID WHERE staff.staffID = '" + id + "';", con).ExecuteReader();
                while (reader.Read())
                {
                    name = reader.GetString(0) + " " +
                        reader.GetString(1);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return name;
        }

        public static void populateTeachingList_UnitUpdate(UnitUpdate UnitUpdateIn)
        {
            try
            {
                UnitUpdate uu = UnitUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataReader reader = new MySqlCommand("SELECT s.staffID, p.firstName, p.surname FROM course c " +
                        "JOIN (unit u JOIN (teaches t JOIN (staff s JOIN person p on s.userID = p.userID) " +
                        "ON t.staffID = s.staffID) ON u.unitID = t.unitID) ON c.courseID = u.courseID " +
                        "WHERE t.unitID = '" + uu.unitIDUpdateCmbx.SelectedItem + "';", con).ExecuteReader();
                while (reader.Read())
                {
                    uu.unitStfUpdateListBox.Items.Add(reader.GetString(0) + " " + reader.GetString(1) + " " + reader.GetString(2));
                }
                con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *-*-*-* Unit Delete Methods *-*-*-*

        public static void FindID_UnitDelete(UnitDelete UnitDeleteIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    UnitDeleteIn.unitIDDeleteCmbx.Items.Add(dt.Rows[i]["unitID"]);
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_UnitDelete(UnitDelete UnitDeleteIn)
        {
            try
            {
                UnitDelete ud = UnitDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM course c JOIN " +
                    "(unit u LEFT JOIN teaches t ON u.unitID = t.unitID) ON c.courseID = u.courseID "
                    + "WHERE u.unitID = '" + ud.unitIDDeleteCmbx.SelectedItem + "';", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);
                ud.unitStfDeleteListBox.Items.Clear();
                ud.unitDelete_CourseTitleTxt.Text = "";
                ud.crseIDDeleteLbl.Content = "";
                ud.unitTitleDeleteTxt.Text = "";
                ud.unitDeleteDescTxt.Text = "";
                if (dt.Rows.Count > 0)
                {
                    //con.Close();
                    ud.unitDelete_CourseTitleTxt.Text = dt.Rows[0]["courseTitle"].ToString();
                    ud.crseIDDeleteLbl.Content = dt.Rows[0]["courseID"].ToString();

                    ud.unitStfDeleteListBox.Items.Clear();
                    populateTeachingList_UnitDelete(ud);

                    ud.unitTitleDeleteTxt.Text = dt.Rows[0]["unitTitle"].ToString();
                    ud.unitDeleteDescTxt.Text = dt.Rows[0]["description"].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteUnit(UnitDelete UnitDeleteIn)
        {
            try
            {
                UnitDelete ud = UnitDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM teaches WHERE unitID = '"
                        + ud.unitIDDeleteCmbx.SelectedItem + "'; DELETE FROM unit WHERE unitID = '"
                        + ud.unitIDDeleteCmbx.SelectedItem + "';");
                    int a = cmd.ExecuteNonQuery();

                    if (a != 0)
                    {
                        con.Close();
                        ud.unitDeletedLblGrid.Visibility = Visibility.Visible;
                        ud.unitDeletedLbl.Content = ("Unit: " + ud.unitIDDeleteCmbx.SelectedItem + " deleted.");
                        ud.unitIDDeleteCmbx.Text = "";
                        ud.unitDelete_CourseTitleTxt.Text= "";
                        ud.crseIDDeleteLbl.Content = "";
                        ud.unitStfDeleteListBox.Items.Clear();
                        ud.unitTitleDeleteTxt.Text = "";
                        ud.unitDeleteDescTxt.Text = "";
                    }

                    else
                    {
                        con.Close();
                        ud.unitDeletedLblGrid.Visibility = Visibility.Visible;
                        ud.unitDeletedLbl.Content = ("Unit: " + ud.unitIDDeleteCmbx.SelectedItem + " not deleted.");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static DataSet populateTeachingList_UnitDelete(UnitDelete UnitDeleteIn)
        {
            try
            {
                UnitDelete ud = UnitDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlCommand cmd = con.CreateCommand();
                
                    cmd.CommandText = string.Format("SELECT s.staffID, p.firstName, p.surname FROM course c " +
                        "JOIN (unit u JOIN (teaches t JOIN (staff s JOIN person p on s.userID = p.userID) " +
                        "ON t.staffID = s.staffID) ON u.unitID = t.unitID) ON c.courseID = u.courseID " +
                        "WHERE t.unitID = '" + ud.unitIDDeleteCmbx.SelectedItem + "';");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    con.Close();

                    da.Fill(ds, "unitTeacher");
                   return ds;
                
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
                return new DataSet();
            }
        }

        // *-*-*-* Unit Search Methods *-*-*-*

        // *************************************************** ASSESSMENT ***************************************************
        
        // *-*-*-* Assessment View Methods *-*-*-*

        public static void populateAsmntListview(AssessmentView AsmntViewIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT a.assessmentID, a.assessmentType, a.assessmentTitle, " +
                        "u.unitTitle, c.courseTitle FROM assessment a JOIN (unit u JOIN course c " +
                        "ON u.courseID = c.courseID) ON a.unitID = u.unitID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "asmnt");

                    foreach (DataRow myRow in ds.Tables["asmnt"].Rows)
                    {
                        AsmntViewIn.displayAsmntDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        // *-*-*-* Assessment Add Methods *-*-*-*

        public static string NextAsmntID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }
            string nextAsmntID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables" +
                    " WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'assessment'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextAsmntID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextAsmntID;
        }

        public static void CreateAssessment(AssessmentAdd AsmntAddIn)
        {
            try
            {
                string nextAsmntID = NextAsmntID();
                string nextMcQuID = NextMcQuID();
                string nextWsID = NextWsID();

                int a = 0;
                int mc1 = 0;
                int mc2 = 0;
                int mc3 = 0;
                int ws1 = 0;
                int ws2 = 0;

                AssessmentAdd aa = AsmntAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM assessment WHERE assessmentID = '"
                        + aa.asmntIDTxt.Text + "';");
                    MySqlDataReader dr = cmd.ExecuteReader();

                    if (dr.HasRows == false)
                    {
                        if (dr.IsClosed == false)
                        {
                            dr.Close();
                        }
                        cmd.CommandText = string.Format("INSERT assessment (assessmentID, assessmentTitle, description, " +
                            "unitID, assessmentType) VALUES ('" + nextAsmntID + "', '" + aa.asmntTitleTxt.Text + "', '" +
                            aa.asmntDescTxt.Text + "', '" + aa.unitIDLbl_AsmntAdd.Content + "', '" +
                            aa.asmntTypeCmbx_Add.Text + "');");
                        a = cmd.ExecuteNonQuery();

                        if (aa.asmntTypeCmbx_Add.SelectedIndex == 3)
                        {
                            cmd.CommandText = string.Format("INSERT wordsearch (wordsearchID, assessmentID) " +
                            "VALUES ('" + nextWsID + "', '" + aa.asmntIDTxt.Text + "');");
                            ws1 = cmd.ExecuteNonQuery();

                            for (int i = 0; i < aa.wsWordsListBox_addAsmnt.Items.Count; i++)
                            {
                                string currentWord = Convert.ToString(aa.wsWordsListBox_addAsmnt.Items[i]);

                                cmd.CommandText = string.Format("INSERT words (word, wordsearchID) " +
                                    "VALUES ('" + currentWord + "', '" + nextWsID + "');");
                                ws2 = cmd.ExecuteNonQuery();
                            }

                            if (a != 0 && ws1 != 0 && ws2 != 0)
                            {
                                //con.Close();
                                aa.wsIDLbl_addAsmnt.Content = NextWsID();
                                aa.wsAddWordTxt_addAsmnt.Text = "";
                                aa.wsWordsListBox_addAsmnt.Items.Clear();
                            }


                            if (a != 0 && (ws1 != 0 && ws2 != 0))
                            {
                                aa.asmntAddedLblGrid.Visibility = Visibility.Visible;
                                aa.asmntAddedLbl.Content = "Assessment: " + nextAsmntID + " Added";

                                aa.asmntIDTxt.Text = NextAsmntID();
                                aa.asmntTitleTxt.Text = "";
                                aa.unitNameCmbx_AsmntAdd.Text = "";
                                aa.unitIDLbl_AsmntAdd.Content = "";
                                aa.asmntTypeCmbx_Add.Text = "";
                                aa.asmntDescTxt.Text = "";
                            }
                        }
                    }

                    if (dr.IsClosed == false)
                    {
                        dr.Close();
                    }

                    if (aa.asmntTypeCmbx_Add.SelectedIndex == 2)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM multiplechoice WHERE mcQuestionID = '"
                        + aa.mcQuIDLbl_addAsmnt.Content + "';");
                        MySqlDataReader mcDr = cmd.ExecuteReader();

                        if (mcDr.HasRows == false)
                        {
                            if (mcDr.IsClosed == false)
                            {
                                mcDr.Close();
                            }
                            cmd.CommandText = string.Format("INSERT multiplechoice (mcQuestionID, mcQuestion, assessmentID) " +
                                "VALUES ('" + nextMcQuID + "', '" + aa.mcQuTxt_addAsmnt.Text + "', '" + aa.asmntIDTxt.Text + "');");
                            mc1 = cmd.ExecuteNonQuery();

                            for (int i = 0; i < aa.mcCorrectLb.Items.Count; i++)
                            {
                                string currentCorrectValue = Convert.ToString(aa.mcCorrectLb.Items[i]);

                                cmd.CommandText = string.Format("INSERT mccorrectanswers (mcCorrectAnswer, mcQuestionID) " +
                                    "VALUES ('" + currentCorrectValue + "', '" + nextMcQuID + "');");
                                mc2 = cmd.ExecuteNonQuery();
                            }

                            for (int i = 0; i < aa.mcAltAnsListBox.Items.Count; i++)
                            {
                                string currentAltValue = Convert.ToString(aa.mcAltAnsListBox.Items[i]);

                                cmd.CommandText = string.Format("INSERT mcaltanswers (mcAltAnswer, mcQuestionID) " +
                               "VALUES ('" + currentAltValue + "', '" + nextMcQuID + "');");
                                mc3 = cmd.ExecuteNonQuery();
                            }

                            if (mc1 != 0 && mc2 != 0 && mc3 != 0)
                            {
                                con.Close();
                                aa.mcQuIDLbl_addAsmnt.Content = NextMcQuID();
                                aa.mcQuTxt_addAsmnt.Text = "";
                                aa.mcCorrectAnsTxt_addAsmnt.Text = "";
                                aa.mcAltAnsTxt_addAsmnt.Text = "";
                                aa.mcCorrectLb.Items.Clear();
                                aa.mcAltAnsListBox.Items.Clear();
                            }
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void FindUnitTitle_AsmntAdd(AssessmentAdd AsmntAddIn)
        {
            try
            {
                AssessmentAdd aa = AsmntAddIn;
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    aa.unitNameCmbx_AsmntAdd.Items.Add(dt.Rows[i]["unitTitle"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void populateUnitID_AsmntAdd(AssessmentAdd AsmntAddIn)
        {
            try
            {
                AssessmentAdd aa = AsmntAddIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit "
                    + "WHERE unitTitle = '" + aa.unitNameCmbx_AsmntAdd.SelectedItem.ToString() + "';", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);
                
                if (dt.Rows.Count > 0)
                {
                    //con.Close();
                    aa.unitIDLbl_AsmntAdd.Content = dt.Rows[0]["unitID"].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        // *** Multiple Choice ***

        public static string NextMcQuID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }
            string nextMcQuID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables " +
                    "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'multipleChoice'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextMcQuID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextMcQuID;
        }


        // *** Wordsearch ***

        public static string NextWsID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }

            string nextWsID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables " +
                    "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wordsearch'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextWsID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextWsID;
        }

        // *-*-*-* Assessment Update Methods *-*-*-*

        public static void FindID_AsmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM assessment", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    AsmntUpdateIn.asmntIDCmbx_updateAsmnt.Items.Add(dt.Rows[i]["assessmentID"]);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public static void FindUnitTitle_AsmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    AsmntUpdateIn.unitNameCmbx_AsmntUpdate.Items.Add(dt.Rows[i]["unitTitle"]);
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateUnitID_AsmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                AssessmentUpdate au = AsmntUpdateIn;

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit "
                    + "WHERE unitTitle = '" + au.unitNameCmbx_AsmntUpdate.SelectedItem.ToString() + "';", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);
                
                if (dt.Rows.Count > 0)
                {
                    //con.Close();
                    au.unitIDLbl_AsmntUpdate.Content = dt.Rows[0]["unitID"].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_asmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                AssessmentUpdate au = AsmntUpdateIn;

                au.asmntTitleTxt.Text = "";
                au.asmntDescTxtB.Text = "";
                au.mcQuIDCmbx_updateAsmnt.Items.Clear();
                au.mcQuTxt_updateAsmnt.Text = "";
                au.mcCorrectAnsListBox.Items.Clear();
                au.mcAltAnsListBox.Items.Clear();
                au.wsWordsListBox.Items.Clear();

                MySqlDataAdapter asmntDA = new MySqlDataAdapter("SELECT * FROM assessment a JOIN unit u " +
                "ON a.unitID = u.unitID WHERE a.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "';", con);
                DataTable asmntDT = new DataTable();

                asmntDA.Fill(asmntDT);

                if (asmntDT.Rows.Count > 0)
                {
                    au.asmntTitleTxt.Text = asmntDT.Rows[0]["assessmentTitle"].ToString();
                    au.unitNameCmbx_AsmntUpdate.SelectedItem = asmntDT.Rows[0]["unitTitle"].ToString();
                    au.unitIDLbl_AsmntUpdate.Content = asmntDT.Rows[0]["unitID"].ToString();
                    au.asmntDescTxtB.Text = asmntDT.Rows[0]["description"].ToString();
                    au.asmntTypeCmbx_Update.Text = asmntDT.Rows[0]["assessmentType"].ToString();
                }
                asmntDA.Dispose();
                asmntDT.Clear();

                if (au.asmntTypeCmbx_Update.SelectedIndex == 2)
                {
                    MySqlDataAdapter da2 = new MySqlDataAdapter("SELECT * FROM multiplechoice "
                        + "WHERE assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "' LIMIT 1;", con);
                    DataTable dt2 = new DataTable();

                    da2.Fill(dt2);
                    DataSet ds = new DataSet();
                    MySqlCommandBuilder asmntsqb = new MySqlCommandBuilder(da2);

                    if (dt2.Rows.Count > 0)
                    {
                        au.mcQuIDCmbx_updateAsmnt.SelectedIndex = 0;
                        au.mcQuTxt_updateAsmnt.Text = dt2.Rows[0]["mcQuestion"].ToString();
                    }
                    da2.Dispose(); 
                    dt2.Clear(); 
                    ds.Clear(); 
                    asmntsqb.Dispose();

                    // --- populate alternative answers ---
                    MySqlDataAdapter mcAltDA = new MySqlDataAdapter("SELECT mcaltanswers.mcAltAnswer FROM mcaltanswers " +
                        "WHERE mcaltanswers.mcQuestionID = '" + au.mcQuIDCmbx_updateAsmnt.Text + "';", con);
                    DataTable mcAltDT = new DataTable();

                    DataSet ds1 = new DataSet();
                    MySqlCommandBuilder mcAltSQB = new MySqlCommandBuilder(mcAltDA);

                    mcAltDA.Fill(ds1, "mcAltAnswer");
                    foreach (DataRow myRow in ds1.Tables["mcAltAnswer"].Rows)
                    {
                        au.mcAltAnsListBox.Items.Add(myRow[0].ToString());
                    }
                    mcAltDA.Dispose(); 
                    mcAltDT.Clear(); 
                    ds1.Clear(); 
                    mcAltSQB.Dispose();

                    // --- populate correct answers ---
                    MySqlDataAdapter mcCorrectDA = new MySqlDataAdapter("SELECT mccorrectanswers.mcCorrectAnswer " + 
                        "FROM mccorrectanswers WHERE mccorrectanswers.mcQuestionID = '"
                        + au.mcQuIDCmbx_updateAsmnt.Text + "';", con);
                    DataTable mcCorrectDT = new DataTable();

                    DataSet ds2 = new DataSet();
                    MySqlCommandBuilder mcCorrectSQB = new MySqlCommandBuilder(mcCorrectDA);

                    mcCorrectDA.Fill(ds2, "mcCorrectAnswer");
                    foreach (DataRow myRow in ds2.Tables["mcCorrectAnswer"].Rows)
                    {
                        au.mcCorrectAnsListBox.Items.Add(myRow[0].ToString());
                    }
                    mcCorrectDA.Dispose();
                    mcCorrectDT.Clear();
                    ds2.Clear();
                    mcCorrectSQB.Dispose();
                }

                if (au.asmntTypeCmbx_Update.SelectedIndex == 3)
                {
                    MySqlDataAdapter wsDA = new MySqlDataAdapter("SELECT * FROM wordsearch "
                        + "WHERE assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "';", con);
                    DataTable wsDT = new DataTable();

                    wsDA.Fill(wsDT);
                    DataSet ds = new DataSet();
                    MySqlCommandBuilder asmntsqb = new MySqlCommandBuilder(wsDA);

                    au.wsIDLbl_updateAsmnt.Content = wsDT.Rows[0]["wordsearchID"].ToString();

                    wsDA.Dispose();
                    wsDT.Clear();
                    ds.Clear();
                    asmntsqb.Dispose();

                    // --- populate wordesarch words answers ---
                    MySqlDataAdapter wsWordDA = new MySqlDataAdapter("SELECT words.word FROM words " +
                        "WHERE words.wordsearchID = '" + au.wsIDLbl_updateAsmnt.Content + "';", con);
                    DataTable wsWordDT = new DataTable();

                    DataSet wsDS = new DataSet();
                    MySqlCommandBuilder wsWordSCB = new MySqlCommandBuilder(wsWordDA);

                    wsWordDA.Fill(wsDS, "word");
                    foreach (DataRow myRow in wsDS.Tables["word"].Rows)
                    {
                        au.wsWordsListBox.Items.Add(myRow[0].ToString());
                    }

                    wsWordDA.Dispose();
                    wsWordDT.Clear();
                    wsDS.Clear();
                    wsWordSCB.Dispose();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public static void UpdateAsmnt(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("UPDATE assessment SET assessmentTitle = '" + au.asmntTitleTxt.Text +
                        "', unitID = '" + au.unitIDLbl_AsmntUpdate.Content + "', description = '" + au.asmntDescTxtB.Text
                        + "', assessmentType = '" + au.asmntTypeCmbx_Update.Text +
                        "' WHERE assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "';");
                    int a = cmd.ExecuteNonQuery();

                    if (au.asmntTypeCmbx_Update.SelectedIndex == 2)
                    {
                        FindMcQuID_AsmntUpdate(AsmntUpdateIn);

                        cmd.CommandText = string.Format("UPDATE multiplechoice SET mcQuestion = '"
                            + au.mcQuTxt_updateAsmnt.Text +
                        "' WHERE assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "';");
                        cmd.ExecuteNonQuery();

                        for (int i = 0; i < au.mcCorrectAnsListBox.Items.Count; i++)
                        {
                            string currentValue = Convert.ToString(au.mcCorrectAnsListBox.Items[i].ToString());

                            using (MySqlCommand cmd2 = con.CreateCommand())
                            {
                                cmd2.CommandText = string.Format("SELECT mccorrectanswers.mcCorrectAnswer FROM assessment JOIN " +
                                    "(multiplechoice JOIN mccorrectanswers " +
                                "ON multiplechoice.mcQuestionID = mccorrectanswers.mcQuestionID) " +
                                "ON assessment.assessmentID = multiplechoice.assessmentID " +
                                "WHERE assessment.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text +
                                "' AND mcaltanswers.mcAltAnswer = '" + currentValue + "';");

                                MySqlDataReader dr1 = cmd.ExecuteReader();
                                if (dr1.HasRows == false)
                                {
                                    if (dr1.IsClosed == false)
                                    {
                                        dr1.Close();
                                    }
                                    cmd2.CommandText = string.Format("INSERT mccorrectanswers (mcCorrectAnswer, mcQuestionID) "
                                        + "VALUES ('" + currentValue + "', '" 
                                        + au.mcQuIDCmbx_updateAsmnt.SelectedValue.ToString() + "');");
                                    cmd2.ExecuteNonQuery();
                                }
                                dr1.Close();
                            }
                        }
                        for (int i = 0; i < au.mcAltAnsListBox.Items.Count; i++)
                        {
                            string currentValue = Convert.ToString(au.mcAltAnsListBox.Items[i].ToString());

                            cmd.CommandText = string.Format("SELECT mcaltanswers.mcAltAnswer FROM assessment JOIN " +
                                "(multiplechoice JOIN mcaltanswers " +
                            "ON multiplechoice.mcQuestionID = mcaltanswers.mcQuestionID) " +
                            "ON assessment.assessmentID = multiplechoice.assessmentID " +
                            "WHERE assessment.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + 
                            "' AND mcaltanswers.mcAltAnswer = '" + currentValue + "';");

                            MySqlDataReader dr2 = cmd.ExecuteReader();
                            if (dr2.HasRows == false)
                            {
                                if (dr2.IsClosed == false)
                                {
                                    dr2.Close();
                                }
                                cmd.CommandText = string.Format("INSERT mcaltanswers (mcAltAnswer, mcQuestionID) " +
                                    "VALUES ('" + currentValue + "', '" + au.mcQuIDCmbx_updateAsmnt.SelectedValue.ToString() + "');");
                                cmd.ExecuteNonQuery();
                            }
                            dr2.Close();
                        }

                        au.mcQuIDCmbx_updateAsmnt.SelectedValue = "";
                        au.mcQuTxt_updateAsmnt.Text = "";
                        au.mcCorrectAnsListBox.Items.Clear();
                        au.mcAltAnsListBox.Items.Clear();
                    }
   
                    if (au.asmntTypeCmbx_Update.SelectedIndex == 3)
                    {
                        for (int i = 0; i < au.wsWordsListBox.Items.Count; i++)
                        {
                            string currentValue = Convert.ToString(au.wsWordsListBox.Items[i].ToString());

                            cmd.CommandText = string.Format("SELECT words.word FROM assessment " +
                                "JOIN (wordsearch JOIN words ON wordsearch.wordsearchID = words.wordsearchID) " +
                                "ON assessment.assessmentID = wordsearch.assessmentID " +
                                "WHERE assessment.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text +
                                "' AND words.word = '" + currentValue + "';");

                            MySqlDataReader dr = cmd.ExecuteReader();

                            if (dr.HasRows == false)
                            {
                                if (dr.IsClosed == false)
                                {
                                    dr.Close();
                                }
                                cmd.CommandText = string.Format("INSERT words (word, wordsearchID) " +
                                     "VALUES ('" + currentValue + "', '" + au.wsIDLbl_updateAsmnt.Content + "');");
                                cmd.ExecuteNonQuery();
                            }
                            if (dr.IsClosed == false)
                            {
                                dr.Close();
                            }
                        }

                        au.wsIDLbl_updateAsmnt.Content = "";
                        au.wsWordTxt_updateAsmnt.Text = "";
                        au.wsWordsListBox.Items.Clear();
                    }

                    if (a != 0)
                    {
                        con.Close();
                        au.asmntUpdatedLblGrid.Visibility = Visibility.Visible;
                        au.asmntUpdatedLbl.Content = ("Assessment: " + au.asmntIDCmbx_updateAsmnt.SelectedItem.ToString() + " updated");
                        au.asmntIDCmbx_updateAsmnt.SelectedIndex = 0;
                        au.asmntTitleTxt.Text = "";
                        au.unitNameCmbx_AsmntUpdate.SelectedIndex = 0;
                        au.unitIDLbl_AsmntUpdate.Content = "";
                        au.asmntTypeCmbx_Update.SelectedIndex = 0;
                        au.asmntDescTxtB.Text = "";
                    }

                    else
                    {
                        au.asmntUpdatedLblGrid.Visibility = Visibility.Visible;
                        au.asmntUpdatedLbl.Content = ("Assessment: " + au.asmntIDCmbx_updateAsmnt.SelectedItem.ToString() + " not updated");
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        // *** Essay ***
        
        // *** Fill in the blanks ***

        // *** Multiple Choice ***

        public static void DeleteMCCorrectAnsRow_updatedAsmnt(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM mccorrectanswers WHERE mccorrectanswers.mcQuestionID "
                        + "IN (SELECT multiplechoice.mcQuestionID FROM multiplechoice " +
                        "WHERE multiplechoice.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.Text + "');");
                    int a = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void DeleteMCAltAnsRow_updateAsmnt(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM mcaltanswers WHERE mcaltanswers.mcQuestionID IN (" +
                        "SELECT multiplechoice.mcQuestionID FROM multiplechoice " +
                        "WHERE multiplechoice.assessmentID = '" + au.asmntIDCmbx_updateAsmnt.SelectedValue + "');");
                    int b = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void AddMCQuestion_updatedAsmnt(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;
                string nextAsmntID = NextAsmntID(); 
                string nextMcQuID = NextMcQuID();

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    int a = 0;
                    int b = 0;
                    int c = 0;
                    
                    cmd.CommandText = string.Format("INSERT multiplechoice (mcQuestionID, mcQuestion, assessmentID) " +
                            "VALUES ('" + nextMcQuID + "', '" + au.mcQuTxt_updateAsmnt.Text + "', '" 
                            + au.asmntIDCmbx_updateAsmnt.Text + "');");
                    a = cmd.ExecuteNonQuery();
                    
                    for (int i = 0; i < au.mcCorrectAnsListBox.Items.Count; i++)
                    {
                        string currentCorrectValue = Convert.ToString(au.mcCorrectAnsListBox.Items[i]);

                        cmd.CommandText = string.Format("INSERT mccorrectanswers (mcCorrectAnswer, mcQuestionID) " +
                            "VALUES ('" + currentCorrectValue + "', '" + nextMcQuID + "');");
                        b = cmd.ExecuteNonQuery();
                    }

                    for (int i = 0; i < au.mcAltAnsListBox.Items.Count; i++)
                    {
                        string currentAltValue = Convert.ToString(au.mcAltAnsListBox.Items[i]);

                        cmd.CommandText = string.Format("INSERT mcaltanswers (mcAltAnswer, mcQuestionID) " +
                        "VALUES ('" + currentAltValue + "', '" + nextMcQuID + "');");
                        c = cmd.ExecuteNonQuery();
                    }

                    if (a != 0 && b != 0 && c != 0)
                    {
                        au.mcQuIDCmbx_updateAsmnt.SelectedValue = NextMcQuID();
                        au.mcQuTxt_updateAsmnt.Text = "";
                        au.mcCorrectAnsTxt.Text = "";
                        au.mcAltAnsTxt.Text = "";
                        au.mcCorrectAnsListBox.Items.Clear();
                        au.mcAltAnsListBox.Items.Clear();
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void FindMcQuID_AsmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;
                
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM multiplechoice WHERE assessmentID = '"
                    + au.asmntIDCmbx_updateAsmnt.SelectedItem.ToString() + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    au.mcQuIDCmbx_updateAsmnt.Items.Add(dt.Rows[i]["mcQuestionID"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static void populateMcQus_asmntUpdate(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                AssessmentUpdate au = AsmntUpdateIn;

                /* au.mcQuIDCmbx_updateAsmnt.Text = "";
                au.mcQuTxt_updateAsmnt.Text = ""; */
                au.mcCorrectAnsListBox.Items.Clear();
                au.mcAltAnsListBox.Items.Clear();

                MySqlDataAdapter da2 = new MySqlDataAdapter("SELECT * FROM multiplechoice "
                    + "WHERE mcQuestionID = '" + au.mcQuIDCmbx_updateAsmnt.SelectedItem.ToString() + "';", con);
                DataTable dt2 = new DataTable();

                da2.Fill(dt2);
                DataSet ds = new DataSet();
                MySqlCommandBuilder asmntsqb = new MySqlCommandBuilder(da2);

                if (dt2.Rows.Count > 0)
                {
                    au.mcQuTxt_updateAsmnt.Text = dt2.Rows[0]["mcQuestion"].ToString();
                }

                // --- populate alternative answers ---
                MySqlDataAdapter mcAltDA = new MySqlDataAdapter("SELECT mcaltanswers.mcAltAnswer FROM mcaltanswers " +
                    "WHERE mcaltanswers.mcQuestionID = '" + au.mcQuIDCmbx_updateAsmnt.SelectedItem.ToString() + "';", con);
                DataTable mcAltDT = new DataTable();

                DataSet ds1 = new DataSet();
                MySqlCommandBuilder mcAltSQB = new MySqlCommandBuilder(mcAltDA);

                mcAltDA.Fill(ds1, "mcAltAnswer");
                foreach (DataRow myRow in ds1.Tables["mcAltAnswer"].Rows)
                {
                    au.mcAltAnsListBox.Items.Add(myRow[0].ToString());
                }

                // --- populate correct answers ---
                MySqlDataAdapter mcCorrectDA = new MySqlDataAdapter("SELECT mccorrectanswers.mcCorrectAnswer " +
                    "FROM mccorrectanswers WHERE mccorrectanswers.mcQuestionID = '"
                    + au.mcQuIDCmbx_updateAsmnt.SelectedItem.ToString() + "';", con);
                DataTable mcCorrectDT = new DataTable();

                DataSet ds2 = new DataSet();
                MySqlCommandBuilder mcCorrectSQB = new MySqlCommandBuilder(mcCorrectDA);

                mcCorrectDA.Fill(ds2, "mcCorrectAnswer");
                foreach (DataRow myRow in ds2.Tables["mcCorrectAnswer"].Rows)
                {
                    au.mcCorrectAnsListBox.Items.Add(myRow[0].ToString());
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        // *** Wordsearch ***

        public static void DeleteWsWordRow_updateAsmnt(AssessmentUpdate AsmntUpdateIn)
        {
            try
            {
                AssessmentUpdate au = AsmntUpdateIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM words w WHERE w.wordsearchID = '"
                        + au.wsIDLbl_updateAsmnt.Content + "' AND word = '" + au.wsWordsListBox.SelectedItem + "');");

                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            cmd.CommandText = string.Format("DELETE FROM words w WHERE w.wordsearchID = '"
                                + au.wsIDLbl_updateAsmnt.Content + "' AND word = '" + au.wsWordsListBox.SelectedItem + "');");
                            cmd.ExecuteNonQuery();
                        }
                        dr.Close();
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        // *-*-*-* Assessment Delete Methods *-*-*-*

        public static void FindID_AsmntDelete(AssessmentDelete AsmntDeleteIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM assessment", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    con.Close();
                    AsmntDeleteIn.asmntIDCmbx_deleteAsmnt.Items.Add(dt.Rows[i]["assessmentID"]);
                }

                // con.Close();
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateFields_asmntDelete(AssessmentDelete AsmntDeleteIn)
        {
            try
            {
                AssessmentDelete ad = AsmntDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM assessment a JOIN unit u ON a.unitID = u.unitID "
                    + "WHERE a.assessmentID = '" + ad.asmntIDCmbx_deleteAsmnt.Text + "';", con);
                DataTable dt = new DataTable();
                con.Close();

                da.Fill(dt);
                ad.asmntTitleTxt.Text = "";
                ad.unitNameTxt_deleteAsmnt.Text = "";
                ad.unitIDLbl_deleteAsmnt.Content = "";
                ad.asmntTypeTxt_deleteAsmnt.Text = "";
                ad.asmntDescTxt.Text = "";

                if (dt.Rows.Count > 0)
                {
                    //con.Close();
                    ad.asmntTitleTxt.Text = dt.Rows[0]["assessmentTitle"].ToString();
                    ad.unitNameTxt_deleteAsmnt.Text = dt.Rows[0]["unitTitle"].ToString();
                    ad.unitIDLbl_deleteAsmnt.Content = dt.Rows[0]["unitID"].ToString();
                    ad.asmntTypeTxt_deleteAsmnt.Text = dt.Rows[0]["assessmentType"].ToString();
                    ad.asmntDescTxt.Text = dt.Rows[0]["description"].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }
        }

        public static void DeleteAsmnt(AssessmentDelete AsmntDeleteIn)
        {
            try
            {
                AssessmentDelete ad = AsmntDeleteIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    if (ad.asmntTypeTxt_deleteAsmnt.Text.Equals("Multiple Choice"))
                    {
                        cmd.CommandText = string.Format("DELETE FROM mccorrectanswers WHERE mccorrectanswers.mcQuestionID "
                        + "IN (SELECT multiplechoice.mcQuestionID FROM multiplechoice " +
                        "WHERE multiplechoice.assessmentID = '" + ad.asmntIDCmbx_deleteAsmnt.SelectedValue + "');");
                        int mc1 = cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("DELETE FROM mcaltanswers WHERE mcaltanswers.mcQuestionID IN (" +
                        "SELECT multiplechoice.mcQuestionID FROM multiplechoice " +
                        "WHERE multiplechoice.assessmentID = '" + ad.asmntIDCmbx_deleteAsmnt.SelectedValue + "');");
                        int mc2 = cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("DELETE FROM multipleChoice WHERE assessmentID = '"
                        + ad.asmntIDCmbx_deleteAsmnt.Text + "';");
                        int mc3 = cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("DELETE FROM assessment WHERE assessmentID = '"
                        + ad.asmntIDCmbx_deleteAsmnt.Text + "';");
                        int mc4 = cmd.ExecuteNonQuery();

                        if (mc4 != 0 && mc3 != 0 || (mc2 != 0 || mc1 != 0))
                        {
                            con.Close();
                            ad.asmntDeletedLblGrid.Visibility = Visibility.Visible;
                            ad.asmntDeletedLbl.Content = ("Assessment: " + ad.asmntIDCmbx_deleteAsmnt.Text + " deleted.");
                            ad.asmntIDCmbx_deleteAsmnt.Text = "";
                            ad.asmntTitleTxt.Text = "";
                            ad.unitNameTxt_deleteAsmnt.Text = "";
                            ad.unitIDLbl_deleteAsmnt.Content = "";
                            ad.asmntTypeTxt_deleteAsmnt.Text = "";
                            ad.asmntDescTxt.Text = "";
                        }
                    }

                    else if (ad.asmntTypeTxt_deleteAsmnt.Text.Equals("Wordsearch"))
                    {
                        cmd.CommandText = string.Format("DELETE FROM words WHERE words.wordsearchID "
                        + "IN (SELECT wordsearch.wordsearchID FROM wordsearch " +
                        "WHERE wordsearch.assessmentID = '" + ad.asmntIDCmbx_deleteAsmnt.SelectedValue + "');");
                        int ws1 = cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("DELETE FROM wordsearch WHERE assessmentID = '"
                        + ad.asmntIDCmbx_deleteAsmnt.Text + "';");
                        int ws2 = cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("DELETE FROM assessment WHERE assessmentID = '"
                        + ad.asmntIDCmbx_deleteAsmnt.Text + "';");
                        int ws3 = cmd.ExecuteNonQuery();

                        if (ws1 != 0 && ws2 != 0)
                        {
                            // con.Close();
                            ad.asmntDeletedLblGrid.Visibility = Visibility.Visible;
                            ad.asmntDeletedLbl.Content = ("Assessment: " + ad.asmntIDCmbx_deleteAsmnt.Text + " deleted.");
                            ad.asmntIDCmbx_deleteAsmnt.Text = "";
                            ad.asmntTitleTxt.Text = "";
                            ad.unitNameTxt_deleteAsmnt.Text = "";
                            ad.unitIDLbl_deleteAsmnt.Content = "";
                            ad.asmntTypeTxt_deleteAsmnt.Text = "";
                            ad.asmntDescTxt.Text = "";
                        }
                    }

                    else
                    {
                        // con.Close();
                        ad.asmntDeletedLblGrid.Visibility = Visibility.Visible;
                        ad.asmntDeletedLbl.Content = ("Assessment: " + ad.asmntIDCmbx_deleteAsmnt.Text + " not deleted");
                    }
                }
            }

            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        // *-*-*-* Assessment Search Methods *-*-*-*

        // *************************************************** LEARNER ***************************************************

        public static String StdtLogin(string userIn, string passwordIn, MainWindow main)
        {

            String stdtName = null;
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                // string enc = Encryption.StaffPwd(passwordIn);

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("Select student.studentID, person.password, person.firstName, " +
                    "person.surname FROM student INNER JOIN person ON student.userID = person.userID " +
                    "WHERE student.studentID = '" + userIn + "' AND person.password = '" + passwordIn + "';");

                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            main.stdtLogged_in();
                            LearnerLoggedIn lrnerLoggedInWindow = new LearnerLoggedIn(main);
                            lrnerLoggedInWindow.Content = null;
                            main.mainContent.Children.Add(lrnerLoggedInWindow.mainContent);

                            stdtName = (dr.GetString(2) + " " + dr.GetString(3));
                            main.userIDLbl.Content = (dr.GetString(0));
                            userX = Convert.ToInt32(main.userIDLbl.Content);
                        }

                        if (dr.HasRows == false)
                        {
                            MessageBox.Show("Access Denied", "Login Information");
                        }
                    }
                }
                con.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            return stdtName;
        }


        // *-*-*-* Take Assessment *-*-*-*

        public static void populateAsmntListview(TakeAssessment takeAsmntIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT a.assessmentID, a.assessmentType, a.assessmentTitle, " +
                        "u.unitTitle, c.courseTitle FROM assessment a JOIN (unit u JOIN course c " +
                        "ON u.courseID = c.courseID) ON a.unitID = u.unitID ORDER BY a.assessmentID ASC;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "asmnt");

                    foreach (DataRow myRow in ds.Tables["asmnt"].Rows)
                    {
                        takeAsmntIn.displayAsmntDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        /*
        public static void populateMcListview(LearnerMultipleChoice lrnerMcIn)
        {
            try
            {
                connection();

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT a.assessmentID, a.assessmentType, a.assessmentTitle, " +
                        "u.unitTitle, c.courseTitle FROM assessment a JOIN (unit u JOIN course c " +
                        "ON u.courseID = c.courseID) ON a.unitID = u.unitID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "asmnt");

                    foreach (DataRow myRow in ds.Tables["asmnt"].Rows)
                    {
                        lrnerMcIn.McLstBox.DataContext = ds.Tables[0].DefaultView;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }
        */

        public static void FindMcQuIDs_LrnerMC(LearnerMultipleChoice LrnerMCIn)
        {
            try
            {
                LearnerMultipleChoice lmc = LrnerMCIn;
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                MySqlDataAdapter daQuIDs = new MySqlDataAdapter("SELECT * FROM multiplechoice WHERE assessmentID = '"
                    + LrnerMCIn.asmntIDLbl.Content + "';", con);
                DataTable dtQuestions = new DataTable();

                daQuIDs.Fill(dtQuestions);

                lmc.mcQuIDsLstbx_lrnerMC.Items.Clear();

                for (int i = 0; i < dtQuestions.Rows.Count; i++)
                {
                    lmc.mcQuIDsLstbx_lrnerMC.Items.Add(dtQuestions.Rows[i]["mcQuestionID"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static List<MultipleChoiceQuestion> PopulateLrnerMc(LearnerMultipleChoice LrnerMcIn, TakeAssessment TakeAsmntIn)
        {
            List<MultipleChoiceQuestion> c = new List<MultipleChoiceQuestion>();
            FindMcQuIDs_LrnerMC(LrnerMcIn);
            
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                //MySqlDataAdapter daAns = new MySqlDataAdapter("SELECT * from multiplechoice where mcQuestionID = '" 
                //    + LrnerMcIn.asmntIDLbl.Content.ToString() + "';", con);
                //DataTable dtAnswers = new DataTable();

                
                
                foreach (Int32 mcID in LrnerMcIn.mcQuIDsLstbx_lrnerMC.Items)
                {
                     MySqlCommand command = new MySqlCommand("SELECT *, 1 as 'Correct' FROM multiplechoice mc "
                    + "JOIN mccorrectanswers mca ON mca.mcQuestionID = mc.mcQuestionID " +
                    "Where mca.mcQuestionID = '" + mcID +
                    "' UNION ALL " +
                    "SELECT *, 0 as 'Correct' FROM multiplechoice mc " +
                    "JOIN mcaltanswers mcw ON mcw.mcQuestionID = mc.mcQuestionID " +
                    "Where mcw.mcQuestionID = '" + mcID + "';", con);
                    
                    MySqlDataReader reader = command.ExecuteReader();

                    int x = 0;
                    MultipleChoiceQuestion q = new MultipleChoiceQuestion();
                    while (reader.Read())
                    {
                       
                        //for (int i = 0; i < LrnerMcIn.mcQuIDsLstbx_lrnerMC.Items.Count; i++)
                        //{
                            if (x == 0)
                                q.question = reader.GetString(1);
                            if (reader.GetBoolean(6))
                                q.addCorrectAnswer(reader.GetString(4));
                            else
                                q.addWrongAnswer(reader.GetString(4));
                            x++;
                        //}
                    }
                    c.Add(q);
                    reader.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
            return c;
        }

        public static List<String> PopulateMC(LearnerMultipleChoice LrnerMcIn)
        {
            List<String> mcQus = new List<string>();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                MySqlDataAdapter da = new MySqlDataAdapter("SELECT a.assessmentTitle, u.unitTitle, u.unitID, " + 
                    "a.description, mc.mcQuestionID, mc.mcQuestion from unit u " +
                    "JOIN (assessment a JOIN multiplechoice mc " +
                    "ON a.assessmentID = mc.assessmentID) ON u.unitID = a.unitID " +
                    "WHERE a.assessmentID = '" + LrnerMcIn.asmntIDLbl.Content.ToString() + "';", con);
                DataTable dt = new DataTable();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    LrnerMcIn.asmntTitleLbl.Content = dt.Rows[0]["assessmentTitle"].ToString();
                    LrnerMcIn.unitIDLbl_mc.Content = dt.Rows[0]["unitID"].ToString();
                    LrnerMcIn.unitNameLbl_mc.Content = dt.Rows[0]["unitTitle"].ToString();
                    LrnerMcIn.mcDescTxtB.Text = dt.Rows[0]["description"].ToString();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM assessment a JOIN unit u ON a.unitID = u.unitID " +
                    "WHERE assessmentID = '" + LrnerMcIn.asmntIDLbl.Content + "';");

                    MySqlDataReader dr = cmd.ExecuteReader();

                    foreach (Int32 mcID in LrnerMcIn.mcQuIDsLstbx_lrnerMC.Items)
                    {
                        while (dr.Read())
                        {
                            if (dr.HasRows == true)
                            {
                                mcQus.Add(dr.GetString(0));
                            }
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

            finally
            {
                con.Close();
            }
            return mcQus;
        }

        public static void SubmitMc(LearnerMultipleChoice lrnerMcIn, MainWindow main)
        {
            try
            {
                LearnerMultipleChoice lmc = lrnerMcIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT assessment_submitted (dateTaken, assessmentID, " +
                        "studentID, result) VALUES (NOW(), '" + lmc.asmntIDLbl.Content +
                        "', '" + userX + "', '" + lmc.resultString + "');");
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }


        public static List<String> PopulateWordsearch(LearnerWordsearch LrnerWsIn, TakeAssessment main)
        {
            List<String> words = new List<string>();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT words.word, unitID FROM words JOIN (wordsearch " +
                        "JOIN assessment ON wordsearch.assessmentID = assessment.assessmentID) " +
                        "ON words.wordsearchID = wordsearch.wordsearchID WHERE assessment.assessmentID = '"
                        + LrnerWsIn.asmntIDLbl.Content.ToString() + "';");

                    MySqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            words.Add(dr.GetString(0).ToUpper());
                        }
                    }

                    MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM unit u " +
                        "INNER JOIN (assessment a INNER JOIN wordsearch w ON w.assessmentID = a.assessmentID) "
                        + "ON u.unitID = a.unitID WHERE a.assessmentID = '" 
                        + LrnerWsIn.asmntIDLbl.Content.ToString() + "';", con);
                    DataTable dt = new DataTable();
                    con.Close();

                    da.Fill(dt);

                    if (dt.Rows.Count > 0)
                    {
                        LrnerWsIn.asmntTitleLbl.Content = dt.Rows[0]["assessmentTitle"].ToString();
                        LrnerWsIn.unitNameLbl_ws.Content = dt.Rows[0]["unitTitle"].ToString();
                        LrnerWsIn.unitIDLbl_ws.Content = dt.Rows[0]["unitID"].ToString();
                        LrnerWsIn.asmntDescTxt.Text = dt.Rows[0]["description1"].ToString();
                        LrnerWsIn.wsIDLbl.Content = dt.Rows[0]["wordsearchID"].ToString();

                        MySqlDataAdapter wsWordDA = new MySqlDataAdapter("SELECT words.word FROM words " +
                        "WHERE words.wordsearchID = '" + LrnerWsIn.wsIDLbl.Content + "';", con);
                        DataTable wsWordDT = new DataTable();

                        DataSet wsDS = new DataSet();
                        MySqlCommandBuilder mcWsWordSCB = new MySqlCommandBuilder(wsWordDA);

                        wsWordDA.Fill(wsDS, "word");
                        foreach (DataRow myRow in wsDS.Tables["word"].Rows)
                        {
                            LrnerWsIn.wsAsmntWordsLstbx.Items.Add(myRow[0].ToString());
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

            finally
            {
                con.Close();
            }
            return words;
        }

        public static void SubmitWs(LearnerWordsearch LrnerWsIn, WordSearch WsIn, MainWindow main)
        {            
            try
            {
                LearnerWordsearch lw = LrnerWsIn;
                WordSearch ws = WsIn;

                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }

                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("INSERT assessment_submitted (dateTaken, assessmentID, " +
                        "studentID, result) VALUES (NOW(), '" + lw.asmntIDLbl.Content + 
                        "', '" + userX + "', '" + ws.resultString + "');");
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public static string NextSubmissionID()
        {
            if (con.State == ConnectionState.Closed)
            {
                connection();
            }
            string nextSbmnID = "0";
            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT AUTO_INCREMENT FROM information_schema.tables " +
                    "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'assessment_submitted'");

                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    nextSbmnID = dr.GetValue(0).ToString();
                }

                dr.Close();
            }
            con.Close();
            return nextSbmnID;
        }

        public static void populateStaffListview(LearnerStaffDetails LrnerStfDetailsIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT p.userID, p.firstName, p.surname, s.jobTitle, p.email, "
                        + "p.telNo FROM person p JOIN staff s ON p.userid = s.userID;");
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "staffPerson");

                    foreach (DataRow myRow in ds.Tables["staffPerson"].Rows)
                    {
                        LrnerStfDetailsIn.displayStaffDetailsLstvw.DataContext = ds.Tables[0].DefaultView;
                    }

                    con.Close();
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void populateAsmntProfileListview(LearnerAssessmentProfile LrnerAsmntProfileIn)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    connection();
                }
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM assessment_submitted asub " +
                    "JOIN (assessment a JOIN unit u ON a.unitID = u.unitID) ON asub.assessmentID = a.assessmentID " +
                    "WHERE asub.dateTaken IN ( " +
                    "SELECT MAX(asub.dateTaken) FROM assessment_submitted asub WHERE asub.studentID = '" + userX +
                    "' GROUP BY asub.assessmentID) ORDER BY asub.dateTaken DESC;");
                    
                    cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                    MySqlCommandBuilder sqb = new MySqlCommandBuilder(da);

                    da.Fill(ds, "asmnt");

                    foreach (DataRow myRow in ds.Tables["asmnt"].Rows)
                    {
                        LrnerAsmntProfileIn.displayAsmntProfileLstvw.DataContext = ds.Tables[0].DefaultView;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

    }
}